Hi guys,
I'm working on a certain automation process, but I've hit a little snag, I have a worksheet called "tabvNetwork" Which holds two columns, one is the name, the other is IP Addresses assigned to that name.
Because of the way the information is extracted to the excel, many of the rows are duplicated on the name field NOT on the IP Address field.
I need to do, is to make sure each name appears only once, but without losing any of the IP Addresses, while keeping the format (comma and single space between every two ip addresses).
I tried doing it with concatenate, but then I have to know in advance how many IP Addresses there's in any row, and even then It comes out very clumsy and cumbersome.
I'm attaching an example file with the actual names changes for obvious reasons. consolifated-duplicates.xlsx
Microsoft ExcelVisual Basic Classic
Last Comment
David Sankovsky
8/22/2022 - Mon
Ryan Chong
try:
Sub test() Dim ws As Worksheet Set ws = Sheets.Add(After:=Sheets(Sheets.Count)) Cells(1, 1) = "VM" Cells(1, 2) = "IP Address" lastRow = Sheets("tabvNetwork").Cells(Sheets("tabvNetwork").Rows.Count, "A").End(xlUp).Row Range("A1:B1").Font.Bold = True tmp = "" j = 1 With Sheets("tabvNetwork") For i = 2 To lastRow If tmp <> .Cells(i, 1) Then tmp = .Cells(i, 1) j = j + 1 ws.Cells(j, 1) = tmp End If If ws.Cells(j, 2) = "" Then ws.Cells(j, 2) = .Cells(i, 2) Else ws.Cells(j, 2) = ws.Cells(j, 2) & ", " & .Cells(i, 2) End If Next End WithEnd Sub
I tried to copy the code to the file that has all the macros I'm using (There are a LOT of things going on in this file) and I skipped the sub the invokes the sub on button click, it'll be called by another sub during the running process. However, when I tried to initiate your subroutine, it has thrown an error about an undefined Variable pointing to "lastRow"
If it's of any relevance, I renamed your subroutine "consolidate" instead of "test" any ideas?
Both worked perfectly after some slight changes.
Thank you all very much. I might incorporate both in different ways as one of the solutions opens a new tab while the other updates the existing one, and I might need both versions in the future. Thanks
Open in new window