Finding duplicate values in 2 different columns ignoring duplicate values in the same column

[Fiona525]

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

[#2]

@Fiona525 

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))))

[#3]

@OliverScheurich

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)

[#4]

PS in my original sheet a lot of other columns are filled with values so the example is simplified, to work good the whole row much be deleted :)

[#5]

@Fiona525 

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.

[#6]

@OliverScheurich 

Thank you, I tried it but my original file kept giving me the message that the formula wasn't right, maybe I'm missing something, I included the original data in a test sheet this time. Can you see what's going wrong?

[#7]

@Fiona525 

If you import data from csv Power Query could do removing of duplicates naturally. 

[#8]

@Fiona525 

Can you attach an excel file (.xlsx file) where the formula and the returned error message are shown? 

I entered data from your original file (8220/BP, ADO1016C-2/C-RS/C, ADO1018C-2/CTPU-BP/C, CLAS2000/B and CLAS3000/B) and it seems to work.

[#9]

@OliverScheurich 

I cannot get it saved, it keeps on giving an error message and then saving is blocked, but I used this formula in the file attached.

=AND(COUNTIF($B$1:$B$21866,B1)>=2,NOT(ISNA(VLOOKUP(B1,$A$1:$A$5,1,FALSE))))

[#10]

@Fiona525 Perhaps if you change the formula to this:

=AND(COUNTIF($B$1:$B$21866,B1)>=2,NOT(ISNA(VLOOKUP(B1,$A$1:$A$316,1,FALSE))))

See attached.

Otherwise, consider @Sergei Baklan 's suggestion and use PowerQuery for such tasks.

[#11]

Thanks for looking at it, we tried to use PowerQuery (Mac version 365, most recent version) but we don't get the options needed for PowerQuery:

https://d.pr/i/VDWus7

[#12]

@Fiona525 Dat is duidelijk! PQ wordt niet ondersteund op de Mac. Maar werkt de formule niet dan? Wat is het probleem?

[#13]

Dat verklaart een hoop! De formule heb ik wel gebruik maar deze geeft een foutmelding, misschien zie ik iets over het hoofd?

https://d.pr/i/Gh6Ym8

[#14]

@Fiona525 Maar je gebruikt de Nederlandse versie! Dan moet je toch Nederlandstalige functies gebruiken, en vermoedelijk ook " ; " tussen de verschillende argumenten. Gebruik zelf de engelse versie en ben dus net gewend aan de NL versie.