Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Duplicate rows in an Excel list | Excel Discussion (Misc queries) | |||
How to concatenate many rows in Excel | Excel Worksheet Functions | |||
No duplicate Concatenate output | Excel Discussion (Misc queries) | |||
How to delete duplicate rows in Excel 2000? | Excel Discussion (Misc queries) | |||
Showing Duplicate rows in excel | Excel Discussion (Misc queries) |