Excel concatenate duplicate rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
Thanks, I'll give it a try

Mike

Don Guillett said:
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
Click to expand...
 
Back
Top