Data compare

[jbbarnabas]

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

A	B	C	Result
100	200	300

Sheet two

A	B	C	Result
100	200	300

 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. 

[#2]

@jbbarnabas 

pls find my solution to your problem

hope it is clear

[#3]

@Ramiz_Assafthank you so much.

Will apply and give feedback

[#4]

@jbbarnabas 

If it works fine, please mark as solved!

[#5]

@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

[#6]

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

[#7]

@jbbarnabas 

As variant, for such sample

is

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

[#8]

Hi @Sergei Baklan 

Thank you very much, will try and revert.

[#9]

Hiu @NikolinoDE

Thank you very much for the information.

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

Thank you 

[#10]

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?

[#11]

Hi @Mr_Hans 

See below

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

Thanks