Forum Discussion
Mollie1247
Copper Contributor
5 sheets - currently they aren't all identical but they can be with little effort if that makes it easier 🙂
Yea_So
May 18, 2021Bronze Contributor
Hi Mollie1247 can you share it?
- Mollie1247May 18, 2021Copper ContributorUnfortunately, no.
It contains confidential information. - Yea_SoMay 18, 2021Bronze Contributor
- SergeiBaklanMay 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. - Mollie1247May 20, 2021Copper ContributorHello - 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? - 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