Comparing 2 non-sequence Sheets in excel to find unique results

[mimranflmsa]

Comparing 2 non-sequence Sheets in excel to find unique results

Hello Everyone,

I hope you all are doing well and staying safe. I have 2 different datasets Sheet 1 and Sheet 2. In both sheets majority data is duplicate but some data in Sheet 1 is unique which I am trying to capture. Is there a way I can do it easily by using any formula to highlight or extract only unique records.

NOTE - In both sheets data is not sequenced which means if value (E78.2) is in H2 row, the same value is in (I2) in sheet 2 which makes difficult to search exact unique/duplicate values in excel for me. I have attached a test file showing Sheet 1, Sheet 2 and a manual unique result sheet as well to better understand what I am looking for.

Your help is much appreciated. Thank you so much for your time and support.

[JeteMc]

If I understand then you could apply the following formula as a conditional formatting rule to cells B2:AA2 on Sheet1:

Please Login or Register  to view this content.

Let us know if you have any questions.

[mimranflmsa]

If I understand then you could apply the following formula as a conditional formatting rule to cells B2:AA2 on Sheet1:

Please Login or Register  to view this content.

Let us know if you have any questions.

Can you please give steps on how to apply the formula using conditional formatting?

[JeteMc]

Select cells B2:AA2 on Sheet1
Select Conditional Formatting
Select New Rule > Use a formula to determine which cells to format
Paste the formula into the Format values where this formula is true window
Select your formatting
Select OK and OK
Let us know if you have any questions.

[mimranflmsa]

Thank you so much JeteMc. I am trying to follow your steps on a new workbook but its not showing correct results. Can you please help me figuring out why?

I truly appreciate your help.

[JohnTopley]

Is this correct ..

[mimranflmsa]

Hi John,

this is 90% correct but still not 100%. if you look at Row # 3 Acct # 30. Your highlighted results are showing as E78.2, F32.9, B18.2 & G40.909 whereas it supposed to show only B18.2 because all other 3 values are present in both sheets 1 & 2 for Account # 30 Row.

[JohnTopley]

This JeteMc's version: not wishing to claim kudos for it nor spread the blame. I'll look again later if JeteMc does'nt beat me to it.

[mimranflmsa]

I really appreciate it if you can. I have been struggling with this for over an year now. Kutools function doesn't work either to show correct results.

[JohnTopley]

Change CF formula

=ISNA(MATCH("*"&B2,Sheet2!$B2:$BO2,0))

wrong range ... could have been my typo!

[mimranflmsa]

You are awesome. Thank you so much. I believe it worked. I just need some more time to verify it on the mass data but first 10 rows looks good now.

[JohnTopley]

All credit is due to JeteMc so if all proves to be OK then please mark as SOLVED ("Thread Tools" at top of first post) and credit JeteMc.

[mimranflmsa]

Absolutely right. Thank you so much JeteMc.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
@JohnTopley, Thank You for your help.