Remove duplicates based on multiple criteria (ID and Date)

[johnnytominaga]

Hey guys!

I'm working on a project that requires merging data from multiple workbooks into a single "master" workbook.
All files have the same number of columns and have been merged properly. That means all data is in the "master" workbook.

I couldn't manage to remove the duplicates though. They need to be removed based on multiple criteria and so that cells with value are copied even if the rest of the row is removed. The criteria that defines which duplicate is to be kept is:
a) ID No. (numerical)
b) Update Date (the higher is kept)

Dataset sample:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Phone[/TD]
[TD]Last updated on[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sara[/TD]
[TD]Miami[/TD]
[TD]99999999[/TD]
[TD]19/07/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Brian[/TD]
[TD][/TD]
[TD]88888888[/TD]
[TD]15/03/2015[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Josh[/TD]
[TD]Seattle[/TD]
[TD][/TD]
[TD]03/02/2015[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Peter[/TD]
[TD]New York[/TD]
[TD][/TD]
[TD]30/09/2016[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD]66666666[/TD]
[TD]01/10/2016[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Brian[/TD]
[TD]Los Angeles[/TD]
[TD]88888888[/TD]
[TD]20/06/2017[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD]20/01/2017[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Nicole[/TD]
[TD][/TD]
[TD]55555555[/TD]
[TD]18/11/2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]77777777[/TD]
[TD]04/01/2017[/TD]
[/TR]
</tbody>[/TABLE]

Desired result:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Phone[/TD]
[TD]Last updated on[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sara[/TD]
[TD]Miami[/TD]
[TD]99999999[/TD]
[TD]19/07/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Brian[/TD]
[TD]Los Angeles[/TD]
[TD]88888888[/TD]
[TD]20/06/2017[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Josh[/TD]
[TD]Seattle[/TD]
[TD]77777777[/TD]
[TD]04/01/2017[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Peter[/TD]
[TD]New York[/TD]
[TD]66666666[/TD]
[TD]20/01/2017[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Nicole[/TD]
[TD][/TD]
[TD]55555555[/TD]
[TD]18/11/2016[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried by using the Dictionary object, but couldn't get anywhere close to what I'm looking to achieve. Also, the script is going to be used in multiple PCs, so if I could avoid needing to activate the Microsoft Scripting Runtime everytime, that would be appreciated.

I'm using Excel 2016 on Windows.

Any ideas on how I could accomplish that?

Thanks a lot in advance.


Johnny

 

[Marcelo Branco]

Don't understand why are you getting an error in
vData = .Range("A2:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row)

It's a very simple code line that defines the variant array

M.

 

[Marcelo Branco]

Tell us what you get in

With Sheets("Sheet1")
    MsgBox .Cells(.Rows.Count, "B").End(xlUp).Row
End with

M.

 

[johnnytominaga]

I'm getting: 6901.

Tell us what you get in

With Sheets("Sheet1")
    MsgBox .Cells(.Rows.Count, "B").End(xlUp).Row
End with

M.

 

[johnnytominaga]

It looks like it starts overflowing between 5000 and 6000.

I'm getting: 6901.

 

[Marcelo Branco]

Inserting an ID in cell B6901, say 4, worked perfectly for me
Sorry, i don't know why you are getting such error. Maybe someone else can help.

M.

 

[johnnytominaga]

I believe the error happens depending on the content/amount of content on the table.
Do you know if there's a limit of how much data a variant can carry?

Nothing to be sorry about.
You were increadibly helpful.

Thanks again.

Inserting an ID in cell B6901, say 4, worked perfectly for me
Sorry, i don't know why you are getting such error. Maybe someone else can help.

M.

 

[Marcelo Branco]

I believe the error happens depending on the content/amount of content on the table.
Do you know if there's a limit of how much data a variant can carry?

As i said i tried with 6901 rows (as you did) and everything worked fine.
No limit that i'm aware to set a variant array. I did some google searches i couldn't find anything saying there is a limit.
But i may be wrong...

Try in a new workbook and see if it works

M.

 

[johnnytominaga]

Hey!

Just an update that I've found out what was happening.
Some cells in the dataset I was testing had a "/" character (in the phones list). I believe that the script was treating that as a calculation to be made, which was what was causing it to crash.

I fixed that and it worked perfectly.

Thanks a lot Marcelo. You were right, there was nothing wrong with the code.
I couldn't have it made without your help.

Have a great day!

As i said i tried with 6901 rows (as you did) and everything worked fine.
No limit that i'm aware to set a variant array. I did some google searches i couldn't find anything saying there is a limit.
But i may be wrong...

Try in a new workbook and see if it works

M.