CODEWITHSUNDEEP
excel
Agony
Agony

Reputation: 27

Use 1 column to "sort" duplicates from 2 other columns?

So here's the task i'm currently having. I have a spreadsheet of close to 74,000 rows and I need to cut down to show JUST rows that are duplicates.

Column B has names and column D has phone numbers so I need to find out if any names in column B match other names in column B or phone numbers in column D match another phone number in column D and if BOTH are NOT duplicates to be able to hide that row using a filter column. I also need it to not count blank cells as duplicates in column D because sometimes the phone number is missing.

Currently I have Column H watching column B and D and it will show "unique" and blank but even on something that IS a duplicate it will not show duplicate.

This is the current formula I have in Column H:

=IF(OR(ISBLANK(D1),TRIM(D1)=""),"BLANK",IF(OR(COUNTIF($B$2,$B2:B74000)>1,COUNTIF($D$2,$D2:D74000)>1),"DUPLICATE","UNIQUE"))

I need column H to show results for every row so I can filter it. It should show Duplicate if column B has a duplicate name OR column D has a duplicate phone number. If not it should show "Unique" and if there's a blank cell in Column D I want it to ignore it and not consider it a duplicate.

Upvotes: 0

Views: 42

Answers (1)

user11655492
user11655492

Reputation: 151

Do you have column headers in row 1? You are checking D1 for blank but counting D2 for duplicates.

Your COUNTIF formulas are backwards (should be COUNTIF(B$2:B$74000, B2)>1 in row 2).

Try these modification in H2 then fill down.

=IF(OR(ISBLANK(D2), TRIM(D2)=""), "BLANK", IF(OR(COUNTIF(B$2:B$74000, B2)>1, COUNTIF(D$2:D$74000, D2)>1), "DUPLICATE", "UNIQUE"))

'add check for duplicate B when D is blank
=IF(AND(OR(ISBLANK(D2), TRIM(D2)=""), COUNTIF(B$2:B$74000, B2)=1), "BLANK", IF(OR(COUNTIF(B$2:B$74000, B2)>1, COUNTIF(D$2:D$74000, D2)>1), "DUPLICATE","UNIQUE"))

Upvotes: 1

Related Questions