Hi
To avoid extra hours puzzling over this, I wondered if anyone could help me with this issue.
I'm cleaning up and consolidating records from 2 previous databases prior to importing them to a new dbase. All the records have been exported to Excel 2007 for now.
We have an issue where we need to identify where more than one record may exist for an individual, and which would which have separate identifier codes. We need to identify those multiple records (by first name and surname), and then ensure that all the records have the same code, replacing any defunct codes with the one we plan to use in the new system.
I have a set of 18000+ rows of data in one tab (sheet 1) of my workbook, and the records we want to cross-reference with (which have the codes we want to use going forwards) in another tab (sheet 2). I have separate fields in both tabs for record code (old and new), first name, surname, plus an additional column for concatenated first name+space+surname in case that might be needed.
What I need to do is:
- cross reference both sets of records to identify where we have duplicate records across both datasets on these separate sheets
- where a duplicate record is identified, transpose the old identifier code (on sheet 1) linked to that record with the new identifier code associated with the matching record on sheet 2 such that we have the same identifier codes for both.
How best to do that? Is it even possible? I've been looking at Index Match formulae but simply haven't been able to make it work.
Any advice welcome - thanks in advance!
To avoid extra hours puzzling over this, I wondered if anyone could help me with this issue.
I'm cleaning up and consolidating records from 2 previous databases prior to importing them to a new dbase. All the records have been exported to Excel 2007 for now.
We have an issue where we need to identify where more than one record may exist for an individual, and which would which have separate identifier codes. We need to identify those multiple records (by first name and surname), and then ensure that all the records have the same code, replacing any defunct codes with the one we plan to use in the new system.
I have a set of 18000+ rows of data in one tab (sheet 1) of my workbook, and the records we want to cross-reference with (which have the codes we want to use going forwards) in another tab (sheet 2). I have separate fields in both tabs for record code (old and new), first name, surname, plus an additional column for concatenated first name+space+surname in case that might be needed.
What I need to do is:
- cross reference both sets of records to identify where we have duplicate records across both datasets on these separate sheets
- where a duplicate record is identified, transpose the old identifier code (on sheet 1) linked to that record with the new identifier code associated with the matching record on sheet 2 such that we have the same identifier codes for both.
How best to do that? Is it even possible? I've been looking at Index Match formulae but simply haven't been able to make it work.
Any advice welcome - thanks in advance!