Forum Discussion

Fiona525's avatar
Fiona525
Copper Contributor
Dec 02, 2021

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 o...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Dec 02, 2021

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

  • Fiona525's avatar
    Fiona525
    Copper Contributor
    Dec 02, 2021

    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)

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Dec 02, 2021

      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.

      • Fiona525's avatar
        Fiona525
        Copper Contributor
        Dec 03, 2021

        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?

    • Fiona525's avatar
      Fiona525
      Copper Contributor
      Dec 02, 2021
      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 🙂

Resources