This will take a slightly different approach. Since you want to compare the two lists prior to merging them then I will layout the easiest method. There are three methods that come to mind; Countif formula method, Excel’s built in Consolidate method, and mine.
1. Copy the records from file #1 to a new sheet.
2. Copy the records from file #2 to the same sheet and past them directly below the first set of records.
3. Sort the records by the Family name.
4. Use this formula in D2, assuming you have no header, D3 if you are using a header and change the cell references to match …..
=if(a3=a2, “Dupe Record”, “Unique”)
5. Add “Unique” to your first record’s column, as this has no formula to fill cell info.
6. Copy Column D from the first record down and Paste Special, Values of the records in Column D, in place. This will allow you to sort on Col. D.
7. Sort Col. D ascending and all your duplicate records will appear at the top which then you can copy, paste back to your first list and have a complete unique list. Or of course copy this to a new sheet that keeps your environment clean and intact.
If this is not what you want please reply.
Doug
