I have a big-ish excel file that has A & B columns. B is a date column, Column A contains names, and sometimes there are multiple entries of some names in each date, I want to remove duplicates of every name within the range of the dates.
I have seen some questions here, but can't find an answer to this specific case.
In 10/29, I would like to keep A, B, only one record from C. On 10/30, I would like to keep A, only one record from D and E.
1 Answer
Copy Columns A & B to another part of the sheet say Columns F & G. Select F & G and from Data Tab use Remove Duplicates and select both columns. Now you have unique records as expected. This works in Excel 2013 and above.
If you have Excel 2003, select both columns and use Menu -- Data --> Filter --> Advanced Filter -- Unique Records only and copy to another location. Preferably have column Headers in Excel 2003 for this to work correctly, else it might consider first row as a column header.
In addition to this you wish to retain the earlier time.
Now put the following formula in Cell H1. In this sample, original data is in Cells A1:C9.
=MIN(IF(F1&G1=$A$1:$A$9&$B$1:$B$9,$C$1:$C$9,""))Press CTRL + SHIFT + ENTER from within the formula bar and create an Array Formula. The formula shall be enclosed in curly braces. Now the earliest Time from matching values shall be extracted. Drag the formula down up to the intended rows.
