Match Function - Checking various sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1634834%22%20slang%3D%22en-US%22%3EMatch%20Function%20-%20Checking%20various%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1634834%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CDIV%20class%3D%22MessageSubject%22%3E%3CDIV%20class%3D%22MessageSubjectIcons%20%22%3E%3CP%3EGood%20day%20community.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20assistance%20please.%3C%2FP%3E%3CP%3EI%20need%20to%20see%20if%20Data%20is%20dupplicated%20in%20various%20sheets.%3C%2FP%3E%3CP%3EI%20currently%20use%20a%20match%20function%20%2Cbut%20I%60m%201unable%20to%20set%20the%20range%20of%20the%20match%20lookup%20array%20to%20all%20sheets%20in%20the%20workbook.%3C%2FP%3E%3CP%3EI%60m%20aware%20that%20the%20Indirect%20function%20may%20be%20of%20assistance%20herein%20%2C%20but%20I%60m%20unable%20to%20incorporate%20the%20indirect%20function%20correctly.%3C%2FP%3E%3CP%3EOr%20if%20there%60s%20any%20way%20to%20extend%20the%20Macth%20Lookup%20array%20to%20multiple%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20assisting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJody%20Barnabas%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1634834%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637827%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Function%20-%20Checking%20various%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760420%22%20target%3D%22_blank%22%3E%40jbbarnabas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20you%20can%20create%20a%20matrix%20where%20you%20can%20count%20if%20there%20are%20duplicates%20between%20cells%20and%20sheets%3C%2FP%3E%3CP%3EI%20have%20tested%20it%2C%20it%20works.%20if%20you%20have%20more%20sheets%20add%20their%20names%20in%20the%20matrix%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebest%20of%20luck%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637854%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Function%20-%20Checking%20various%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760420%22%20target%3D%22_blank%22%3E%40jbbarnabas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20rather%20depends%20on%20the%20layout%20of%20the%20data%20you%20are%20trying%20to%20compare.%26nbsp%3B%20If%20you%20were%20looking%20for%20duplicate%20records%20in%20similar%20tables%20then%20Power%20Query%20might%20be%20to%20best%20solution.%26nbsp%3B%20An%20Append%20Query%26nbsp%3B%20would%20combine%20the%20Tables%20in%20memory.%26nbsp%3B%20Then%20first%20'Keep%20duplicates'%20and%20then%20'Remove%20Duplicates'%2C%20to%20avoid%20retaining%20multiple%20copies%20of%20the%20duplicated%20records%2C%20would%20provide%20a%20list%20of%20the%20records%20held%20in%20duplicate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1641385%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Function%20-%20Checking%20various%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1641385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40Ramiz_Assaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3EI%60ll%20see%20if%20i%20can%20work%20around%20your%20suggestion.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1641397%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Function%20-%20Checking%20various%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1641397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20example%20as%20below%3A%3C%2FP%3E%3CP%3EThe%20result%20needs%20to%20tell%20me%20when%20a%20row%20data%20is%20duplicated%20in%20one%20of%20the%20other%20sheets%3C%2FP%3E%3CP%3EMain%20Sheet%3A%3C%2FP%3E%3CTABLE%20width%3D%22663%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2290%22%3EVendor%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPrice%3C%2FTD%3E%3CTD%20width%3D%22104%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%22179%22%3EProduct%3C%2FTD%3E%3CTD%20width%3D%22226%22%3EDupplication%20Result%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA.Small%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E01.01.2020%3C%2FTD%3E%3CTD%3EPC%20Mouse%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB.Big%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E01.02.2020%3C%2FTD%3E%3CTD%3EPC%20Screen%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC.%20Average%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E01.03.2020%3C%2FTD%3E%3CTD%3EKeyboard%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%3C%2FP%3E%3CTABLE%20width%3D%22437%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2290%22%3EVendor%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPrice%3C%2FTD%3E%3CTD%20width%3D%22104%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%22179%22%3EProduct%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA.Small%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E01.03.2020%3C%2FTD%3E%3CTD%3EKeyboard%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB.Big%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E01.02.2020%3C%2FTD%3E%3CTD%3EPC%20Screen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC.%20Average%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E01.04.2020%3C%2FTD%3E%3CTD%3EPC%20Mouse%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%3C%2FP%3E%3CTABLE%20width%3D%22437%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2290%22%3EVendor%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPrice%3C%2FTD%3E%3CTD%20width%3D%22104%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%22179%22%3EProduct%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA.Small%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E01.01.2020%3C%2FTD%3E%3CTD%3ESoftware%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB.Big%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E01.02.2020%3C%2FTD%3E%3CTD%3EPrinter%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC.%20Average%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E01.03.2020%3C%2FTD%3E%3CTD%3EWIFI%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 
7 Replies

@jbbarnabas 

 

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

@jbbarnabas 

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.

@Ramiz_Assaf 

Thank you.

I`ll see if i can work around your suggestion.

Thank you

@Peter Bartholomew 

 

The example as below:

The result needs to tell me when a row data is duplicated in one of the other sheets

Main Sheet:

VendorPriceDateProductDupplication Result
A.Small20001.01.2020PC Mouse 
B.Big30001.02.2020PC Screen 
C. Average50001.03.2020Keyboard 

 

Sheet 1

VendorPriceDateProduct
A.Small20001.03.2020Keyboard
B.Big30001.02.2020PC Screen
C. Average50001.04.2020PC Mouse

 

Sheet 2

VendorPriceDateProduct
A.Small20001.01.2020Software
B.Big30001.02.2020Printer
C. Average50001.03.2020WIFI

 

Thank you. 

 

@jbbarnabas 

Straightforward way is

image.png

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

@Sergei Baklan 

For now this formula works perfect.

Thanks for your input.

Hope someone will assist with a more dynamic approach.

 

Thank you.  

@jbbarnabas , you are welcome