Unique data

[Linda]

We are trying to sort data based on identification nos. from location A to B.

Example of data

Location A Location B
123 466
476 741
001 004
466 123


1st and 4th rows actually refers to the same thing. Is there a way to sort
this so that we can eliminate duplicates in "pairs" in data? Pls help...

 

[Jacob Skaria]

With your data in ColA/B with headers try the below formula in cell C2 and
copy down as required. The formula will return "Duplicate" for the first
duplicate entry.

=IF(ISERROR(INDEX(A2:A$100,MATCH(1,(A2:$A$100=B2)*(B2:$B$100=A2),0))),"","Duplicate")

Please note that this is an array formula; press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula>}"

If this post helps click Yes

 

[Jacob Skaria]

You can also try the below nomal formula in cell c2 and copy down

=IF(SUMPRODUCT((A2:A$100=B2)*(B2:B$100=A2)),"Duplicate","")

If this post helps click Yes