# Complex (Possibly) Formula Assistance Required - Find Duplicates

Occasional Contributor

# 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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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.

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

Hi @Mollie1247  can you share it?

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

Unfortunately, no.

It contains confidential information.

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

Get 20-hours of Excel 2016 training here ► https://www.simonsezit.com/course-category/excel/During this Microsoft Excel 2016 training tutorial video, discove...

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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.

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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?

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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

# Re: Complex (Possibly) Formula Assistance Required - Find Duplicates

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