how to identify duplicates in one column based on a common value in another column?

%3CLINGO-SUB%20id%3D%22lingo-sub-1410619%22%20slang%3D%22en-US%22%3Ehow%20to%20identify%20duplicates%20in%20one%20column%20based%20on%20a%20common%20value%20in%20another%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410619%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20quite%20a%20while%20an%20automation%20was%20adding%20duplicate%20contacts%20to%20a%20Project%2C%20so%20a%20person%20is%20named%20sometimes%20many%20times%20on%20a%20Project.%20I%20need%20to%20delete%20the%20duplicates%20(shown%20below%20in%20%3CSTRONG%3Ebold%3C%2FSTRONG%3E).%26nbsp%3B%20I%20have%20a%20two-column%20spreadsheet%20with%3A%3C%2FP%3E%3COL%3E%3CLI%3EProject%3C%2FLI%3E%3CLI%3EProject%20Contact(s)%20-%20ok%20to%20have%20multiple%20people%2C%20but%20never%20the%20same%20name%20more%20than%20once.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIf%20a%20Project%20has%20more%20than%20one%20Project%20Contact%2C%20there%20is%20a%20row%20for%20each%20of%20those%20contacts%2C%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2244.44444631783782%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3ERed%20Project%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3EMary%20Jones%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3E%3CSTRONG%3ERed%20Project%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3E%3CSTRONG%3EMary%20Jones%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3ERed%20Project%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3EJim%20Stone%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3E%3CP%3EBlue%20Project%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3EBart%20Brown%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3E%3CP%3E%3CSTRONG%3EBlue%20Project%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2229px%22%3E%3CSTRONG%3EBart%20Brown%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20need%20to%20sort%20by%20the%20Project%2C%20but%20how%20do%20I%20identify%20which%20names%20(B)%20are%20duplicate%20names%20on%20the%20same%20Project%20(A)%3F%20Is%20there%20a%20feature%20in%20Conditional%20Formatting%20or%20Remove%20Duplicates%20that%20will%20allow%20me%20to%20do%20this%3F%20I%20need%20something%20to%20flag%20the%20duplicate%20rows%20so%20I%20can%20delete%20them%2C%20but%20I%20don't%20know%20the%20tools%20well%20enough%20for%20this%20one.%20I've%20included%20a%20small%20sample%20of%20real%20data%2C%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1410619%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410634%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20identify%20duplicates%20in%20one%20column%20based%20on%20a%20common%20value%20in%20another%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410634%22%20slang%3D%22en-US%22%3EBack%20up%20your%20file%20before%20trying.%3CBR%20%2F%3E%3CBR%20%2F%3ESelect%2Fhighlight%20your%20table.%3CBR%20%2F%3EClick%20Data%2C%20Remove%20Duplicates.%3CBR%20%2F%3ECheck%20the%20boxes%20for%20the%20columns%20that%20contain%20project%20name%20and%20person%20name%20(uncheck%20any%20others).%3CBR%20%2F%3EClick%20OK.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415309%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20identify%20duplicates%20in%20one%20column%20based%20on%20a%20common%20value%20in%20another%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415309%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676417%22%20target%3D%22_blank%22%3E%40sharphaus%3C%2FA%3E%26nbsp%3BIn%20order%20to%20see%20the%20duplicated%20rows%2C%20select%20your%20both%20column%20data%20range.%20Conditional%20Formatting%2FHighlight%20Cell%20rules%2FDuplicate%20Values%2FOK%3C%2FP%3E%3CP%3EAll%20duplicated%20rows%20in%20your%20data%20range%20should%20be%20highlighted%20in%20red%20fill%20with%20red%20text%20(Important%20note%3A%20the%20highlighted%20rows%20help%20you%20to%20see%20which%20rows%20are%20duplicated%20but%20they%20should%20never%20been%20used%20for%20deletion%20because%20if%20you%20delete%20all%20highlighted%20rows%20you%20are%20also%20deleting%20the%201st%20occurrence%20of%20the%20duplicates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20dataset%20is%20small%2C%20you%20can%20follow%20the%20instructions%20in%20the%20other%20reply%20to%20remove%20the%20duplicated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20because%20duplicate%20removal%20cannot%20be%20undone%2C%20to%20ensure%20that%20I%20only%20remove%20the%20duplicated%20data%20when%20my%20dataset%20is%20large%2C%20I%20usually%20do%20this%20way%3A%3C%2FP%3E%3CP%3E1.%20Assume%20your%20data%20is%20A2%3AB7%20(A1%3AB1%20is%20heading)%3C%2FP%3E%3CP%3E2.%20In%20C1%20type%20Count%20as%20the%20heading%20and%20in%20C2%20type%3A%26nbsp%3B%3DCOUNTIFS(A2%3A%24A%247%2CA2%2CB2%3A%24B%247%2CB2)%3C%2FP%3E%3CP%3ENote%20that%20you%20need%20to%20make%20the%20ending%20cell%20of%20the%20data%20range%20in%20the%20above%20formula%20as%20absolute%20reference%20with%20%24%20sign%2C%20whereas%20the%20opening%20cell%20is%20relative%20reference%20without%20%24%20sign)%3C%2FP%3E%3CP%3E3.%20Add%20filter%20to%20the%20heading%20of%20A1%3AC1%3C%2FP%3E%3CP%3E4.%20Filter%20column%20C%20for%20any%20number%20larger%20than%201%20(i.e.%202%2C%203%2C%20etc)%20then%20delete%20those%20filtered%20rows%3C%2FP%3E%3CP%3E5.%20Unfilter%20column%20C%20and%20now%20you%20should%20expect%20to%20see%20only%20the%20number%201%20in%20that%20column%2C%20meaning%20any%20row%20shown%20is%20unique.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

For quite a while an automation was adding duplicate contacts to a Project, so a person is named sometimes many times on a Project. I need to delete the duplicates (shown below in bold).  I have a two-column spreadsheet with:

  1. Project
  2. Project Contact(s) - ok to have multiple people, but never the same name more than once.

If a Project has more than one Project Contact, there is a row for each of those contacts, like this:

 

Red ProjectMary Jones
Red ProjectMary Jones
Red ProjectJim Stone

Blue Project

Bart Brown

Blue Project

Bart Brown

 

I know I need to sort by the Project, but how do I identify which names (B) are duplicate names on the same Project (A)? Is there a feature in Conditional Formatting or Remove Duplicates that will allow me to do this? I need something to flag the duplicate rows so I can delete them, but I don't know the tools well enough for this one. I've included a small sample of real data, thanks!

 

 

2 Replies
Highlighted
Back up your file before trying.

Select/highlight your table.
Click Data, Remove Duplicates.
Check the boxes for the columns that contain project name and person name (uncheck any others).
Click OK.

Highlighted

@sharphaus In order to see the duplicated rows, select your both column data range. Conditional Formatting/Highlight Cell rules/Duplicate Values/OK

All duplicated rows in your data range should be highlighted in red fill with red text (Important note: the highlighted rows help you to see which rows are duplicated but they should never been used for deletion because if you delete all highlighted rows you are also deleting the 1st occurrence of the duplicates)

 

If your dataset is small, you can follow the instructions in the other reply to remove the duplicated.

 

However, because duplicate removal cannot be undone, to ensure that I only remove the duplicated data when my dataset is large, I usually do this way:

1. Assume your data is A2:B7 (A1:B1 is heading)

2. In C1 type Count as the heading and in C2 type: =COUNTIFS(A2:$A$7,A2,B2:$B$7,B2)

Note that you need to make the ending cell of the data range in the above formula as absolute reference with $ sign, whereas the opening cell is relative reference without $ sign)

3. Add filter to the heading of A1:C1

4. Filter column C for any number larger than 1 (i.e. 2, 3, etc) then delete those filtered rows

5. Unfilter column C and now you should expect to see only the number 1 in that column, meaning any row shown is unique.