I was given 6 spreadsheets combined into one (the reason is immaterial-but valid) it is what it is... unfortunately! (42 columns, 14,000 rows of which 450 names are duplicated as illustrated below)
I want to merge duplicate data.. but not the way I see it shown in all the 'merge' and the 'delete duplicate' threads I have seen so far.
I manage a homeless shelter and someone duplicated the spreadsheets and started using the duplicates to enter fresh data.... several times!
As a result I now have somewhere around 8 instances, (or 7,5,3 etc) of every homeless guest, but it has duplicate data in some columns, and new data in others, for instance:
I want to turn this....
Name SS# 1st entry date, 2nd entry date, 3rd entry date, fourth entry date, etc...
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011, 5/16/2012
John Smith, 123-45-6789, 1/5/2010,
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011,
John Smith, 123-45-6789, 1/5/2010, 7/13/2010,
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011, 5/16/2012
Eric Brown, 234,45,4321, 2/7/2010, 3/19/2011, 4/4/2012, 12/19/2012
Eric Brown, 234,45,4321, 2/7/2010, 3/19/2011, 4/4/2012,
Eric Brown, 234,45,4321, 2/7/2010, 3/19/2011,
Eric Brown, 234,45,4321, 2/7/2010, 3/19/2011, 4/4/2012, 12/19/2012
Eric Brown, 234,45,4321, 2/7/2010, 3/19/2011, 4/4/2012,
Eric Brown, 234,45,4321, 2/7/2010,
into this....(solution desired)
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011, 5/16/2012
Eric Brown, 234,45,4321, 2/7/2010, 3/19/2011, 4/4/2012, 12/19/2012
I tried de-duping based on duplicate rows and that helped the pure duplicates get out of the mix, but if I try to to de-dup on only the first two columns of data it may remove date I want to keep.
So I need to de-dupe and merge at the same time. I am not a spreadsheet guru.. anyone have any suggestions?
Much obliged,
Simon
I'm thinking it needs to be something like: if column 2 is identical, then merge(if blank) or replace (if same data) all other columns.
Bookmarks