Complex (Possibly) Formula Assistance Required - Find Duplicates

[Mollie1247]

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 🙂

[#2]

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

[#3]

@Yea_So 

Sheet 1:

File Name	ID	Part
	A14141	1
	A32691	2

Sheet 2:

File Name	ID	Part
	A14141	6
	A32691	2

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

[#4]

Hi @Mollie1247 

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

[#5]

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

[#6]

Hi @Mollie1247 

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

[#7]

5 sheets - currently they aren't all identical but they can be with little effort if that makes it easier 🙂

[#8]

Hi @Mollie1247  can you share it?

[#9]

Unfortunately, no.

It contains confidential information.

[#10]

Hi @Mollie1247 

https://youtu.be/BDHX8GT6UpQ

[#11]

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

[#12]

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.

[#13]

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?

[#14]

@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

[#15]

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