Forum Discussion
Solved
Date Column Duplicates
I am fairly new to using excel and have a problem trying to find duplicate dates in a column for a specific value in another column. For example, in the column below, Jane has a duplicate date of service of 1/20/2018. Bob also had a date of service on 1/20/18 but since it was not a duplicate for him, I do not want this to be resulted as a duplicate. Any help is greatly appreciated.
| Name | Date_of_Service |
| Bob | 1/20/2018 |
| Jane | 1/20/2018 |
| Jane | 1/20/2018 |
| Mary | 1/30/2018 |
| John | 2/1/2018 |
| Tom | 2/5/2018 |
| Bill | 2/6/2018 |
| Bill | 2/7/2018 |
| Ted | 2/9/2018 |
Hi
If you wish to keep the original data then you can use the COUNTIFS formula in the screenshot below
3 Replies
Hi
If you wish to keep the original data then you can use the COUNTIFS formula in the screenshot below
If you add an IF condition before that formula Wyn indicated, you can fastly remove duplicates.
C2: IF(countifs($A$1:A2;A2)>1;"";A2)
C3: IF(countifs($B$1:B2;B2)>1;"";B2)
Then you have let's say a "copy" of columns A and B in columns C and D with blank rows if there's a duplicate. Now you copy columns C and D and paste them as values (special paste) in another sheet or on other columns or on whatever colums you want; now you delete all other columns and sort datas by date or by name or whatever.
Hi,
You might like to remove the duplicate in this table, so please select any cell in the table, then go to:
Data (Ribbon Tab) >> Data Tools >> Remove Duplicates
When the Remove Duplicates dialog box pops up, make sure that all columns of this table are selected, and then hit OK.
I hope this helps you
Regards
