David Sankovsky

asked on 8/17/2015

Consolidate duplicate rows with String as DATA

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

8/17/2015

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 With
End Sub

Open in new window

consolifated-duplicates-b.xlsm

Rgonzo1971

8/17/2015

Hi,

pls try

Sub Macro()

LastRow = Range("B" & Cells.Rows.Count).End(xlUp).Row
For Idx = LastRow To 2 Step -1
    If Cells(Idx - 1, 1) = Cells(Idx, 1) Then
        Cells(Idx - 1, 2) = Cells(Idx - 1, 2) & ", " & Cells(Idx, 2)
        Cells(Idx, 2).EntireRow.Delete
    End If
Next

End Sub

Open in new window

Regards

David Sankovsky

8/17/2015

ASKER

Thanks, to the both of you, I'll try both solutions and let you know how it went.
Thanks.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

David Sankovsky

8/17/2015

ASKER

Hi Ryan Chong,

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?

ASKER CERTIFIED SOLUTION

Rgonzo1971

8/17/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

SOLUTION

Ryan Chong

8/17/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

David Sankovsky

8/17/2015

ASKER

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