CODEWITHSUNDEEP
excelexcel-formula
Noob_Programmer
Noob_Programmer

Reputation: 147

Excel matching duplicates across multiple columns with unorganized data

I got two lists each containing the following parameters: Serial number, name of product and name of the owner.

I wish to be able to compare the two lists to be able to mark duplicates where a duplicate must have all three categories identical. One list usually has more lines of items and the lists are not organized.

Usually I would use this formula:

=IF(COUNTIF($B:$B,$A:$A)=0, " ", " V ")

to check unorganized values across one parameter but I am not sure how to do this for all three parameters. Can anyone suggest a solution?

Upvotes: 1

Views: 742

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

With sample data like this:

enter image description here

The formula is:

=IFERROR(INDEX(MATCH(E2&F2&G2,$A$2:$A$8&$B$2:$B$8&$C$2:$C$8,0),1)>0,FALSE)

Which:

  • takes the combo of the row of the list you are checking E2&F2&G2
  • tries to MATCH it against all the rows of the other list $A$2:$A$8&$B$2:$B$8&$C$2:$C$8
  • returns TRUE for a match - using INDEX to pull out the result
  • returns FALSE for an error in the MATCH (#VALUE! error)

Upvotes: 1

Related Questions