Need to compare data on sheet 1 vs sheet 2 and show results on sheet 3

Locate $ difference.xlsxHello & i appreciate your time (in advance),

I have data from Yesterdays orders on Sheet 1. Data from Todays orders on Sheet 2. On sheet 3 I want Excel to use the concatenated Order # in column D on sheet 2 (Today), look for the exact match in column D on Sheet 1 (Yesterday), and IF a match is found compare the pricing in column BT from both sheets. And continue the same for each Order # on sheet 2.

After all that, on the results on sheet 3 I want to add conditional formatting to show:
1) If Today's pricing is more than Yesterday's (aka OVER, fill colour GREEN on the Order #)
2) If Today's pricing is less that Yesterday's (aka UNDER, fill colour RED on the Order #)
3) If Today has an order that wasn't on yesterdays list (aka NEW, fill colour YELLOW on the Order #)
4) If Today's pricing is the same as Yesterday's (no colour needed on the Order #)

What I need to know from the results is if an order from yesterday is still waiting to be shipped today, if that order's pricing has changed, and what the difference in pricing is.

I have attached a sample of the very large Data dump. I need the columns to stay the same as this is how the information is transferred from our database. Also just a heads up, a normal day's data will actually be between 400 and 800 rows long, but i shortened the example to just a couple rows.

Sheet 4 is what I hope the results will look like on sheet 3.

Please let me know if you need more info.

Hi

Review Sheet
Cell A9

Formula:

Please Login or Register  to view this content.

Ctrl+Shift+Enter then copy down

Cell B9

Formula:

Please Login or Register  to view this content.

Copy down

Cell C9

Formula:

Please Login or Register  to view this content.

Copy down

Cell D9

Formula:

Please Login or Register  to view this content.

Copy down

See the file and see Conditional Formatting

Regard
micope21

I have used your sheet2 to make the calculations. I compared "Today" column D with "Yesterday" column D and if there was a match, carried the match to sheet2 then used that value to determine the $ amounts. Then I applied the Conditional Formatting.

VLOOKUP can be use instead of the INDEX MATCH formula in column A

Formula:

Please Login or Register  to view this content.

Ok I expanded the ranges for the formulae to go to row 2000. If you examine the formulae you will see what I did and this is the change that you will need to make to go beyond that limit.

Change =INDEX($BT$2:$BT$1000 and do same match to 1000 or more 2000.

Regard
micope21