Forum Discussion

Mollie1247's avatar
Mollie1247
Copper Contributor
May 12, 2021

Complex (Possibly) Formula Assistance Required - Find Duplicates

Hi all!   New to the community and really hoping someone can help!   I currently have 2 sheets in Excel (I think 365) that I am hoping to find duplicates on, let's just call them Sheet 1 and Shee...
admin
Excel
Formulas and Functions
Yea_So's avatar
Yea_So
Bronze Contributor

Hi Mollie1247  can you share it?

Mollie1247's avatar
Mollie1247
Copper Contributor
May 18, 2021
Unfortunately, no.

It contains confidential information.
  • Mollie1247's avatar
    Mollie1247
    Copper Contributor
    May 20, 2021
    Hello - I'm struggling to see how power query can assist me....

    It's finding the duplicates in the query, but I need those duplicates highlighted in the original tables.... Can you advise?
  • Mollie1247's avatar
    Mollie1247
    Copper Contributor
    May 20, 2021
    Hey Sergei,

    Not really sure I understand your COUNTIF function - I've tried applying it to my spreadsheet but it brings back all 0's..... which I know is incorrect.

    I'm trying to compare sheets 2,3,4,5 with sheet 1 and would like to avoid merging the sheets is possible.
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 18, 2021

    Mollie1247 

    If the purpose is to find duplicates you may add helper column to Sheet2 with formula like

    =COUNTIFS(Sheet1!B:B,B2:B300,Sheet1!C:C,C2:C300)

    (assuming you are on 365) and filter on values not equal to zero, it returns duplicates.

     

    If to merge without duplicates it's better to convert ranges in the sheets into structured tables and merge with

    =LET(
  fst,  FILTER(Table1,COUNTIFS(Table1[ID],Table2[ID],Table1[Part],Table2[Part])),
  snd,  FILTER(Table2,COUNTIFS(Table2[ID],Table1[ID],Table2[Part],Table1[Part])),
  rfst, ROWS(fst),
  rsnd, ROWS(snd),
  k,    SEQUENCE(rfst+rsnd),
  IF(k<=rfst, INDEX(fst, k, {1,2}), INDEX(snd, k-rfst, {1,2}))
)

Resources