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 Sheet 2 for simplicity sake.

 

I need to find out whether the File ID (Column 2/Sheet 1) and the Part (Column 3/Sheet 1) are duplicate values of the File ID (Column 3/Sheet 2) and (Column 4/Sheet 2).

 

So the following example would be a duplicate:

Column 2 / Sheet 1 was 123456 AND Column 3 / Sheet 1 was 4 

AND

Column 3 / Sheet 2 was 123456 AND Column 3 / Sheet 1 was 4

 

HOWEVER this example would NOT:

Column 2 / Sheet 1 was 123456 AND Column 3 / Sheet 1 was 4 

AND

Column 3 / Sheet 2 was 123456 AND Column 3 / Sheet 1 was 2

 

Can anyone help? I have a thousand rows to check and can't afford to do each one individually!

 

Thanks 🙂

 

14 Replies

  • Yea_So's avatar
    Yea_So
    Bronze Contributor

    Mollie1247 

    If

    So the following example would be a duplicate:

    Column 2 / Sheet 1 was 123456 AND Column 3 / Sheet 1 was 4 

    AND

    Column 3 / Sheet 2 was 123456 AND Column 3 / Sheet 1 was 4

     

    then what is on column 2 sheet 1?  <==== my question might seem irrelevant but believe me it is.

    • Mollie1247's avatar
      Mollie1247
      Copper Contributor

      Yea_So 

       

      Sheet 1:

      File NameIDPart
       A141411
       A326912

       

      Sheet 2:

      File NameIDPart
       A141416
       A326912

       

      Hopefully this helps!

      So Row one would NOT be a duplicate because it is a different part - but row 2 would because both column  2 AND 3 match

      • Yea_So's avatar
        Yea_So
        Bronze Contributor

        Hi Mollie1247 

         

        If both worksheets are formatted that way you can use power query to remove duplicates in a snap.

Resources