|
Excel concatenate duplicate rows
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 |
Excel concatenate duplicate rows
Hi,
Any chance I could get a sample of the code that would do this? Mike "Don Guillett" wrote: 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. Server1,Serial#,OS,BIOS, Ver, NIC1,NIC2,NIC3 Server1,Serial#,OS,BIOS, Ver, NIC1,2,3 -- Don Guillett SalesAid Software "MFelkins" wrote in message ... 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 |
Excel concatenate duplicate rows
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 -- Don Guillett SalesAid Software "MFelkins" wrote in message ... Hi, Any chance I could get a sample of the code that would do this? Mike "Don Guillett" wrote: 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. Server1,Serial#,OS,BIOS, Ver, NIC1,NIC2,NIC3 Server1,Serial#,OS,BIOS, Ver, NIC1,2,3 -- Don Guillett SalesAid Software "MFelkins" wrote in message ... 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 |
Excel concatenate duplicate rows
Thanks, I'll give it a try
Mike "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "MFelkins" wrote in message ... Hi, Any chance I could get a sample of the code that would do this? Mike "Don Guillett" wrote: 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. Server1,Serial#,OS,BIOS, Ver, NIC1,NIC2,NIC3 Server1,Serial#,OS,BIOS, Ver, NIC1,2,3 -- Don Guillett SalesAid Software "MFelkins" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com