Forum Discussion
Mollie1247
Copper Contributor
Unfortunately, no.
It contains confidential information.
It contains confidential information.
SergeiBaklan
May 18, 2021MVP
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}))
)
- Mollie1247May 20, 2021Copper ContributorHey 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. - SergeiBaklanMay 20, 2021MVP
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