Comparing 2 Spreadsheets, Highlight Certain Cells Based on Two Criteria

oldfashionedgal

New Member
Joined
Oct 18, 2017
Messages
2
I have tried to find a forum response that fits my particular scenario, and I have found similar ones but not quite what I need. I have two different spreadsheets which I would like to compare two pieces of criteria and only if BOTH criteria are met, then I would like cells in a particular column to highlight. First, I would like both spreadsheets to compare both the Name and ID# fields to see if there are any matches between new and existing customers. Then, if there are any customer matches, I would like it to take THOSE customers and highlight their Date Related cells that are less than the Date Opened cell on the New Accounts spreadsheet. I have attempted this function below and it has not worked. Not sure what I am doing wrong or if this is even possible.

=AND(‘New AccountsÂ’!$D2:$E2=$D2:$E2,$F2<Â'New AccountsÂ’!$A$2)

Ideally, this function would result in the Existing Accounts F2 and F3 being highlighted. Any help would be greatly appreciated.

Thanks,
oldfashionedgal


Spreadsheet 1 Title: New Accounts
A B C D E
Date Opened Account # Service Name ID #
10/17/17 111222 aaa Luc Leith 98765
10/17/17 111222 aaa Luc Leith 98765
10/17/17 111222 bbb Luc Leith 98765
10/17/17 222333 ccc Alex D 43210
10/17/17 333444 ddd Alex D 43210
10/17/17 555666 fff Jon Snow 65432


Spreadsheet 2 Title: Existing Accounts
A B C D E F
Date Opened Account # Service Name ID # Date Related
9/9/17 1234 aaa Jon Snow 65432 9/9/17
5/2/16 5678 bbb Luc Leith 98765 7/1/16
1/20/01 3456 ccc Queen Mary 76543 10/1/07
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
@oldfashionedgal, Select F2:F??? (however far down your data goes) in the Existing Accounts sheet and apply the following conditional formatting formula:

Code:
=IFERROR(SUMPRODUCT(--(MATCH(D2,'New Accounts'!$D$2:$D$50000,0))*(MATCH(E2,'New Accounts'!$E$2:$E$50000,0))*(F2<'New Accounts'!$A$2:$A$50000))>0,FALSE)

Then [Format...] with the highlight color of your choice in the Fill tab.

If you have more or fewer than 50000 rows in the New Accounts sheet, adjust those three "50000" in the formula to be whatever you consider a "safe margin" that reflects the highest row count you'll ever expect to reach on the sheet.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top