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
LVL 9
David SankovskySenior SysAdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Rgonzo1971Commented:
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 SankovskySenior SysAdminAuthor Commented:
Thanks, to the both of you, I'll try both solutions and let you know how it went.
Thanks.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
David SankovskySenior SysAdminAuthor Commented:
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?
Rgonzo1971Commented:
then try

Sub Macro()
Dim LastRow as Long, Idx as Long
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I skipped the sub the invokes the sub on button click
Yes, I added the button for testing purposes.

>>it has thrown an error about an undefined Variable pointing to "lastRow"
I guess you got Option Explicit on. so try define the variables accordingly.

Sub consolidate()
    Dim i As Long
    Dim j As Long
   Dim lastRow As Long
    Dim tmp As String
    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

David SankovskySenior SysAdminAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.