Finding similar records in excel

Hello Experts

I have 4 columns of records in an excel file with almost thousand rows of data. I need to show highlights of similar records across two columns such as System1, Record1 and System2,Record2. For example in the attached file, I need result highlighted in color for similar records such as db2 and abcd for both system1 and system2 across 4 columns. I could do using vlookup for 2 column comparison but here if system1 and Record1 values match with system2 and record2 values, I want it highlighted. Is there a simpler solution using built in conditional formatting instead of vba?

Thanks for your help in advance.
uniqueAndDifferences.xlsx
techEverestAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
A very simple way is to insert this formula in E2 and copy it down

=IF(OR(A2<>C2,B2<>D2),"")
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Select the whole columns from A to D and use the following formula to set a New Rule for Conditional Formatting and set the format as per your choice.

=AND($A1&$B1&$C1&$D1<>"",$A1=$C1,$B1=$D1)

Open in new window

For details refer to the attached workbook. Is this what you are trying to achieve?
uniqueAndDifferences.xlsx
techEverestAuthor Commented:
Thanks for your quick response. I applied both the above formulas but didnt get the desired output. I have included each of your formula in column F and G. Row 2 data matches and should have been true but the result is FALSE. Same result in column G and blanks.. What i want is for example in the attached sheet.if system1 =db2 and record1=abcd , system2=db2 and record2=abcd then the formula should give True result set and the like for rest of the records(non matching should show false)
Copy-of-uniqueAndDifferences-1.xlsx
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saqib Husain, SyedEngineerCommented:
My formula works as intended. It will place a false next to the line which was intended to be highlighted.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You have to change the formula which I suggested like below since you have changed the columns.
=AND($A1&$B1&$D1&$E1<>"",$A1=$D1,$B1=$E1)

Open in new window

I have applied the conditional formatting in the attached workbook so just change the data or add new data to test it.
uniqueAndDifferences-1.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
techEverestAuthor Commented:
Sorry for the delay and thanks for your help.  I actually concatenated  two columns records system1 and record1 as one column and did the same for system2 and record2 columns. I then used vlookup which is much easier to use and gives correct result for larger data sets.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.