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 03, 2021

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.

Fiona525's avatar
Fiona525
Copper Contributor
Dec 04, 2021

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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 04, 2021

    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 SergeiBaklan 's suggestion and use PowerQuery for such tasks.

    • Fiona525's avatar
      Fiona525
      Copper Contributor
      Dec 04, 2021
      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
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Dec 04, 2021

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

Resources