Complex (Possibly) Formula Assistance Required - Find Duplicates

Copper Contributor

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

@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.

@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

Hi @Mollie1247 

 

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

I've never used power query before...

There's actually more than one sheet - but all will be compared to sheet 1.

All sheets vary from 50 rows - a couple of thousand....

Hi @Mollie1247 

 

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

5 sheets - currently they aren't all identical but they can be with little effort if that makes it easier :)
Unfortunately, no.

It contains confidential information.
During this Microsoft Excel 2016 training tutorial video, discover how to use Power Query / Get and Transform in Microsoft Excel 2016 ✅ Get 20-hours of Excel 2016 training here:➡️ https://www.simonsezit.com/course-category/excel/ 🌍 Get unlimited training with Simon Sez IT's 150+ courses ⏩ ...

@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}))
)
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.
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 

How COUNTIFS() works is here

COUNTIFS function - Office Support (microsoft.com)

How to use the Excel COUNTIFS function | Exceljet

 

Why it doesn't work in your case - hard to say without the sample file.

If you need to highlight duplicates you may apply conditional formatting rule with practically the same formula

image.png

Hi @Mollie1247 

 

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

Yea_So_0-1621533555578.png

and Sheet2, this is how it looks:

Yea_So_1-1621533603509.png

My question is:

are all the other Sheets similar to Sheet2?  Thanks