Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Hi,
pls try
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
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?
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?
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
>>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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial