# Data compare

Occasional Contributor

# Data compare

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.

10 Replies

# Re: Data compare

pls find my solution to your problem

hope it is clear

# Re: Data compare

@Ramiz_Assafthank you so much.

Will apply and give feedback

# Re: Data compare

If it works fine, please mark as solved!

# Re: Data compare

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

# Betreff: Data compare

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

# Re: Data compare

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")``

# Re: Data compare

Thank you very much, will try and revert.

# Betreff: Data compare

Hiu @Nikolino

Thank you very much for the information.

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

Thank you

# Re: Data compare

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?

# Re: Data compare

Hi @Mr_Hans

See below

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

Thanks