Data compare

%3CLINGO-SUB%20id%3D%22lingo-sub-1590530%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590530%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%3Epls%20find%20my%20solution%20to%20your%20problem%3C%2FP%3E%3CP%3Ehope%20it%20is%20clear%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590676%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590676%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%40ramizassaf%3C%2FA%3Ethank%20you%20so%20much.%3C%2FP%3E%3CP%3EWill%20apply%20and%20give%20feedback%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590387%22%20slang%3D%22en-US%22%3EData%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590387%22%20slang%3D%22en-US%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%3EIs%20there%20a%20formula%20that%20i%20can%20use%20to%20compare%20two%20sheets%20for%20dupplicate%20data%20rows.%3C%2FP%3E%3CP%3EThe%20compare%20formula%20needs%20to%20take%20all%20the%20data%20of%20the%20second%20sheet%20in%20consideration%20and%20not%20only%20the%20row%20that%20will%20be%20comapred.%3C%2FP%3E%3CP%3EThe%20result%20needs%20to%20give%20me%20a%20%22Match%22%20answer%2C%20and%20if%20possible%20where%20the%20matched%20data%20is%20located%20in%20the%20second%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3EExp%3A%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%20one%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22256%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EResult%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E100%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ESheet%20two%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22256%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2263.7%22%20height%3D%2214%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2263.71%22%20height%3D%2214%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2263.71%22%20height%3D%2214%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2263.88%22%20height%3D%2214%22%3EResult%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.7%22%20height%3D%2214%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2263.71%22%20height%3D%2214%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2263.71%22%20height%3D%2214%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2263.88%22%20height%3D%2214%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263.7%22%20height%3D%2214%22%3E100%3C%2FTD%3E%3CTD%20width%3D%2263.71%22%20height%3D%2214%22%3E200%3C%2FTD%3E%3CTD%20width%3D%2263.71%22%20height%3D%2214%22%3E300%3C%2FTD%3E%3CTD%20width%3D%2263.88%22%20height%3D%2214%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3BI%60m%20currently%20using%20a%20IF%20formula%2C%20but%20the%20arguement%20fails%20if%20the%20location%20of%20the%20row%20in%20either%20of%20the%20sheets%20is%20changed%20or%20not%20on%20the%20same%20position%20in%20both%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20excel%202016%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJody.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1590387%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590691%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590691%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%3EIf%20it%20works%20fine%2C%20please%20mark%20as%20solved!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1592536%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1592536%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%40ramizassaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Ramizassaf.%3C%2FP%3E%3CP%3ENo%20my%20problem%20has%20not%20been%20solved.%3C%2FP%3E%3CP%3EThe%20formulas%20(X-lookup%20and%20CONCAT)%20used%20in%20your%20spreadsheet%20is%20not%20available%20in%20my%20version%20of%20Excel%2C%20currently%20using%20Excel%202016.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1592788%22%20slang%3D%22de-DE%22%3ESubject%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1592788%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CFONT%20size%3D%224%22%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%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3Emaybe%20this%20info%20will%20help%20you%20further%20...%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3ECompare%20two%20versions%20of%20a%20workbook%20by%20using%20Spreadsheet%20Compare%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fcompare-two-versions-of-a-workbook-by-using-spreadsheet-compare-0e1627fd-ce14-4c33-9ab1-8ea82c6a5a7e%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fcompare-two-versions-of-a-workbook-by-using-spreadsheet-compare-0e1627fd-ce14-4c33-9ab1-8ea82c6a5a7e%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3ECompare%20two%20or%20more%20worksheets%20at%20the%20same%20time%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fcompare-two-or-more-worksheets-at-the-same-time-1deed3da-a297-4260-98aa-a7b2d90c81ab%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fcompare-two-or-more-worksheets-at-the-same-time-1deed3da-a297-4260-98aa-a7b2d90c81ab%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20find%20out%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1601097%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1601097%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%0A%3CP%3EAs%20variant%2C%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20414px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F213449i7D9914605153F730%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eis%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(MATCH(A1%26amp%3BB1%26amp%3BC1%2CSheet2!%24A%241%3A%24A%241000%26amp%3BSheet2!%24B%241%3A%24B%241000%26amp%3BSheet2!%24C%241%3A%24C%241000%2C0)%2C%22nu%20duplication%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1601403%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1601403%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%2C%20will%20try%20and%20revert.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1601406%22%20slang%3D%22en-US%22%3EBetreff%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1601406%22%20slang%3D%22en-US%22%3E%3CP%3EHiu%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20the%20information.%3C%2FP%3E%3CP%3EHowever%20I%60m%20looking%20to%20the%20verification%20within%20one%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1601636%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1601636%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20send%20your%20current%20(not%20fully%20working)%20formula%20mentioned%20here%3A%3C%2FP%3E%3CP%3E%26gt%3B%26gt%3B%20%26nbsp%3BI%60m%20currently%20using%20a%20IF%20formula%2C%20but%20the%20arguement%20fails%20if%20the%20location%20of%20the%20row%20in%20either%20of%20the%20sheets%20is%20changed%20or%20not%20on%20the%20same%20position%20in%20both%20sheets.%3CBR%20%2F%3EI%20like%20the%20the%20match%20formula%20suggestion%2C%20it%20sould%20work%2C%20maybe%20easy%20to%20adapt%20if%20I%20can%20see%20your%20current%20faulty%20version%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1603440%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20compare%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603440%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F765849%22%20target%3D%22_blank%22%3E%40Mr_Hans%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20below%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(%24A%242%3D'Sheet2'!%24A%242%2C%22Pass%22%2C%22Data%20not%20aligned%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThanks%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Good day community.

 

I need assistance please.

Is there a formula that i can use to compare two sheets for dupplicate data rows.

The compare formula needs to take all the data of the second sheet in consideration and not only the row that will be comapred.

The result needs to give me a "Match" answer, and if possible where the matched data is located in the second sheet. 

Exp: 

Sheet one

ABCResult
100200300 
    

Sheet two

ABCResult
    
100200300 

 I`m currently using a IF formula, but the arguement fails if the location of the row in either of the sheets is changed or not on the same position in both sheets. 

Using excel 2016

 

Thanks in advance for the help.

 

Jody. 

 

10 Replies
Highlighted

@jbbarnabas 

pls find my solution to your problem

hope it is clear

Highlighted

@Ramiz_Assafthank you so much.

Will apply and give feedback

Highlighted

@jbbarnabas 

If it works fine, please mark as solved!

Highlighted

@Ramiz_Assaf 

Hi Ramizassaf.

No my problem has not been solved.

The formulas (X-lookup and CONCAT) used in your spreadsheet is not available in my version of Excel, currently using Excel 2016.

 

Thank you

Highlighted

@jbbarnabas 

maybe this info will help you further ...

 

Compare two versions of a workbook by using Spreadsheet Compare

https://support.microsoft.com/en-gb/office/compare-two-versions-of-a-workbook-by-using-spreadsheet-c...

 

Compare two or more worksheets at the same time

https://support.microsoft.com/en-gb/office/compare-two-or-more-worksheets-at-the-same-time-1deed3da-...

 

I would be happy to find out if I could help.

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@jbbarnabas 

As variant, for such sample

image.png

is

=IFNA(MATCH(A1&B1&C1,Sheet2!$A$1:$A$1000&Sheet2!$B$1:$B$1000&Sheet2!$C$1:$C$1000,0),"nu duplication")
Highlighted

Hi @Sergei Baklan 

Thank you very much, will try and revert.

Highlighted

Hiu @Nikolino

 

Thank you very much for the information.

However I`m looking to the verification within one workbook.

 

Thank you 

Highlighted

Please send your current (not fully working) formula mentioned here:

>>  I`m currently using a IF formula, but the arguement fails if the location of the row in either of the sheets is changed or not on the same position in both sheets.
I like the the match formula suggestion, it sould work, maybe easy to adapt if I can see your current faulty version?

Highlighted

Hi @Mr_Hans 

See below

=IF($A$2='Sheet2'!$A$2,"Pass","Data not aligned")

 

Thanks