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
May 18, 2021

Hi Mollie1247 

 

How many sheets? I imagine all the sheets have identical in tables (columns)?

Mollie1247's avatar
Mollie1247
Copper Contributor
May 18, 2021
5 sheets - currently they aren't all identical but they can be with little effort if that makes it easier 🙂
  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    May 20, 2021

    Hi Mollie1247 

     

    From what I can understand in how you're describing the geography of Sheet1, this is how it looks:

    and Sheet2, this is how it looks:

    My question is:

    are all the other Sheets similar to Sheet2?  Thanks

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    May 18, 2021

    Hi Mollie1247  can you share it?

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

      It contains confidential information.
      • 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