Forum Discussion
Match Function - Checking various sheets
7 Replies
It rather depends on the layout of the data you are trying to compare. If you were looking for duplicate records in similar tables then Power Query might be to best solution. An Append Query would combine the Tables in memory. Then first 'Keep duplicates' and then 'Remove Duplicates', to avoid retaining multiple copies of the duplicated records, would provide a list of the records held in duplicate.
The example as below:
The result needs to tell me when a row data is duplicated in one of the other sheets
Main Sheet:
Vendor Price Date Product Dupplication Result A.Small 200 01.01.2020 PC Mouse B.Big 300 01.02.2020 PC Screen C. Average 500 01.03.2020 Keyboard Sheet 1
Vendor Price Date Product A.Small 200 01.03.2020 Keyboard B.Big 300 01.02.2020 PC Screen C. Average 500 01.04.2020 PC Mouse Sheet 2
Vendor Price Date Product A.Small 200 01.01.2020 Software B.Big 300 01.02.2020 Printer C. Average 500 01.03.2020 WIFI Thank you.
Straightforward way is
but it's much better to use dynamic ranges (or fixed but not entire columns).
Actually the same idea with Power Query (table at the right).
I thought you can create a matrix where you can count if there are duplicates between cells and sheets
I have tested it, it works. if you have more sheets add their names in the matrix
best of luck
