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:

ID	Name	Location	Phone	Last updated on
1	Sara	Miami	99999999	19/07/2018
3	Brian		88888888	15/03/2015
4	Josh	Seattle		03/02/2015
7	Peter	New York		30/09/2016
7	Peter		66666666	01/10/2016
3	Brian	Los Angeles	88888888	20/06/2017
7	Peter			20/01/2017
9	Nicole		55555555	18/11/2016
4			77777777	04/01/2017

<tbody>
</tbody>

Desired result:

ID	Name	Location	Phone	Last updated on
1	Sara	Miami	99999999	19/07/2018
3	Brian	Los Angeles	88888888	20/06/2017
4	Josh	Seattle	77777777	04/01/2017
7	Peter	New York	66666666	20/01/2017
9	Nicole		55555555	18/11/2016

<tbody>
</tbody>

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.