Excel duplicates removal but with a twist !

[charleskavazy]

I would really appreciate some Excel help to remove "partial duplicates but with a twist".

Here is an example of what I mean:

Eg. I have two rows , row one is Fred Blogs Cat (in columns A, B and C on row 1) and the second row, Fred Blogs Dog (in columns A, B and C on row 2) and I want to end up with one row Fred Blogs Cat Dog (in columns A, B, C and D on row 1) and the second row is deleted.

I have over 80,000 rows to deal with so some help would be really, really appreciated!

 

[Domski]

Hi,

Can there be more than 2 entries per name combination?

Dom

 

[charleskavazy]

Hi Domski

Thanks for responding

Unfortunately yes there can be more than 2. I think 4 rows is the maximum.

 

[Domski]

Try this but make sure you've got you're data saved as have not thoroughly tested it:

Sub De-Dup_Sort_Of()
Dim lastRow As Long, myRow As Long, writeRow As Long, writeCol As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
writeCol = 3
writeRow = 2
For myRow = 2 To lastRow
If Cells(myRow, 1) & "_" & Cells(myRow, 2) = Cells(myRow + 1, 1) & "_" & Cells(myRow + 1, 2) Then
writeCol = writeCol + 1
Cells(writeRow, writeCol) = Cells(myRow + 1, 3)
Else
writeCol = 3
writeRow = myRow + 1
End If
Next myRow
For myRow = lastRow To 2 Step -1
If Cells(myRow, 1) & "_" & Cells(myRow, 2) = Cells(myRow - 1, 1) & "_" & Cells(myRow - 1, 2) Then
Cells(myRow, 1).EntireRow.Delete
End If
Next myRow
End Sub

Sort your data by column A and B before you run it.

Hope it helps,

Dom

 

[charleskavazy]

Thanks for that. I will give it a go. Really appreciate your help.