Comparison of 2 lots of data and display difference with a visual aid

[pmw19800]

hello All

I’d like to compare 2 lots of data (firm tab) and (Customer Demand Tab) and display the difference on a 3rd tab (Increase or decrease)
The 3rd tab needs to display with some kind of visual indicator to show it’s an increase of decrease or no change.


I’ve highlighted in red what I’d like it to do, But I have no idea if this is possible or not.

[dflak]

Since you have Excel 2010, I used tables since they know how big they are (formulas always reference exactly the right amount of data), the grow and shrink as you copy / paste data into them, the formulas "copy down automatically" and you can use natural-language like syntax.

The differences are made by looking up the part number on one table and subtracting them from the lookup on the other table. The ISERROR is there because most of the parts don't match. I added a couple of helper columns to be able to filter on those parts that exist in both tables.

=IFERROR( VLOOKUP([@Part],Table_Firm,D$1,FALSE)-VLOOKUP([@Part],Table_Demand,D$1,FALSE),"")

From there it was a bit of conditional formatting.

[pmw19800]

That’s great, but there is 2 slight issues’,

Issue 1
When I paste the relevant data into the respective tabs the date doesn’t change on the increase or Decrease tab,

Issue 2
After updating with current data, When I save the
Document and reopen its seems to be losing data and gives a warning message of some kind

Paul

[dflak]

I bet that happened because you completely overwrote the tables when copying them in. We can take a different approach.

Tell me how this workbook is filled out. Apparently the data on the first two sheets are copied and pasted in daily. Where does the data for the comparison worksheet come from and is it copied and pasted too?

[pmw19800]

Hello

i have pasted the data in its raw format under tabs raw Firm and Raw Demand.

Tab Increase and decrease is a list of all the part numbers my customer can possibly order, The Firm Demand is what I have frozen on day A, and Customer Demand is the actual demand on Day B

Both lots of data are pasted into each tab as it’s displayed in its raw format.