Excel duplicates removal but with a twist !

charleskavazy

New Member
Joined
Jun 19, 2008
Messages
3
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Can there be more than 2 entries per name combination?

Dom
 

charleskavazy

New Member
Joined
Jun 19, 2008
Messages
3
Hi Domski

Thanks for responding

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

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Try this but make sure you've got you're data saved as have not thoroughly tested it:

Code:
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
 

Forum statistics

Threads
1,085,495
Messages
5,384,007
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top