Forum Discussion
Finding duplicate values in 2 different columns ignoring duplicate values in the same column
Hello,
finding duplicate values is no problem but when comparing column A and column B and column B has multiple duplicate values of it's own it will count these as well as duplicates. How do I only compare column A and column B on duplicate values and ignoring duplicate values which are only found in the same column(B)?
So in the example we're looking for A,B,D,H and K in column B but the duplicates of per example value C in column B must be ignored.
Using the duplicate function will also highlight the duplicate values in column B which are not in column A.
Thanks
13 Replies
Are you looking for a rule for conditional formatting?
=AND(COUNTIF($B$1:$B$33,B1)>=2,NOT(ISNA(VLOOKUP(B1,$A$1:$A$5,1,FALSE))))
Thanks for your reaction, this could work: ideally I'm looking for a formula that deletes the complete rows of the values that are not containing duplicate values (see attachment)
With the suggested formula you can sort columns B and C by column C. The result is as shown in attached file. Then you can delete rows with result false in column C. After this the remaining data is the same as shown in columns F and G.
