Sep 04 2020 01:48 AM
Good day community.
I need assistance please.
I need to see if Data is dupplicated in various sheets.
I currently use a match function ,but I`m 1unable to set the range of the match lookup array to all sheets in the workbook.
I`m aware that the Indirect function may be of assistance herein , but I`m unable to incorporate the indirect function correctly.
Or if there`s any way to extend the Macth Lookup array to multiple sheets.
Thank you for assisting.
Jody Barnabas
Sep 04 2020 10:37 PM
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
Sep 04 2020 11:53 PM - edited Sep 04 2020 11:54 PM
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.
Sep 07 2020 06:15 AM
Sep 07 2020 06:24 AM
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.
Sep 07 2020 07:06 AM
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).
Sep 08 2020 02:07 AM
For now this formula works perfect.
Thanks for your input.
Hope someone will assist with a more dynamic approach.
Thank you.