johnnytominaga
New Member
- Joined
- Apr 27, 2018
- Messages
- 19
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
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