Excel concatenate duplicate rows

[MFelkins]

Hi,

I get a report from Dell OMSA that list all my dell servers and the hardware
and firmware versions. The report lists each server in a row with its
attributes to the right. So far, so good. If the server has multiple NIC's it
will create a row for each NIC. So I can have multiple entries for each
server, only differing in the NIC column. What I want is one row per server.
If there are multiple NIC's or other hardware, I want that information tacked
onto the end of the row. And duplicate information ignored.

Sample Rows

Server1,Serial#,OS,BIOS, Ver, NIC1
Server1,Serial#,OS,BIOS, Ver, NIC2
Server1,Serial#,OS,BIOS, Ver, NIC3

Desired output

Server1,Serial#,OS,BIOS, Ver, NIC1,NIC2,NIC3, etc

Any ideas?

Mike

 

[Don Guillett]

Doable with a list (a macro can make it) and then a macro to loop thru and
append to the list. Easier to code
if less than 10. You could do it either way show below.

 

[MFelkins]

Hi,

Any chance I could get a sample of the code that would do this?

Mike

 

[Don Guillett]

The simplest form of this assumes that:
your imported list is in a1:a?
you have a list in c1:c?
Server1,Serial#,OS,BIOS, Ver
Server2,Serial#,OS,BIOS, Ver

You have no more than 9 servers

Sub GetNICS()
x = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & x)
x = Mid(c, 7, 1)
Cells(x, "c") = Cells(x, "c") & Right(Cells(c.Row, 1), 6)
Next
End Sub

 

[MFelkins]

Thanks, I'll give it a try

Mike

The simplest form of this assumes that:
your imported list is in a1:a?
you have a list in c1:c?
Server1,Serial#,OS,BIOS, Ver
Server2,Serial#,OS,BIOS, Ver

You have no more than 9 servers

Sub GetNICS()
x = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & x)
x = Mid(c, 7, 1)
Cells(x, "c") = Cells(x, "c") & Right(Cells(c.Row, 1), 6)
Next
End Sub