Need to compare two sets of data

[PattiK323]

I have two tabs within a workbook.

1)Tab 1 -2014 Forecast data by business unit object account, and subsidiary account. E.g., 123456.7890.0001
2)Tab 2 - 2015 budget data by business unit, object, account, and subsidiary account.

Within the tab of 2014 Forecast Data, most BU-Obj Acct-sub combinations have two lines of data - one for the actual (AA) ledger, showing through Oct-14, and one for budget (BW) ledger, Nov-14 and Dec-14. I only want to see one line item total on my comparison (e.g., AA and BW ledgers combined).

I want to compare the two sets of data to see the following:

1) What is 2015 Budget vs. 2014 Forecast by business unit, object account and subsidiary account?

Example:
2015 Budget 2014 Forecast Difference
123456.7890.0001 $9,876 $5,432 $4,444

2) Are there line items that have 2014 Forecast activity but, that have not been budgeted in 2015?

Example:
2015 Budget 2014 Forecast Difference
132465.1234.0002 $- $10,000 ($10,000)

3) Are there any line items that are budgeted for 2015; but, that have no 2014 forecast activity?

Example:

102030.4567.0003 $10,000 $- $10,000

Thanks in advance to anyone and everyone that can help!

 

[dcardno]

You could move all the data into one tab (just cut/paste or copy/paste) and then put it into a pivot table, but I would do the following:
1) create a new tab
2) cut paste all the BU-Obj Acct-sub combinations (just the row labels, not the data) from both forecast and budget tabs into that new tab - then sort them
3) filter the labels to extract the unique values
4) assuming the data labels are in column A in all sheets, and the data is in column B, on the new tab enter a sumproduct formula in cell B1 something like

=sumproduct(--(A1 = [forecast tab]!$A$1:$A$5000), [forecast tab]!$B$1:$B$1)

(obviously the exact syntax depends on the tab names)
5) this should extract the total of forecast + year-to-date for each BU-Obj Acct-sub combination
6) test whether the formula has worked properly by comparing the sums of all data on the two sheets
7) insert a similar formula in column C on the new tab, referencing the budget tab as the data source.

You can now compare differences between 2014 forecast and 2015 budget, as well as 2015 nil-budget entries, either by inspection, by sorting the data, or conditional highlighting.

 

[PattiK323]

Thank you. How do I "filter the labels to extra the unique values?"

 

[dcardno]

Select the entire range of labels - they should be sorted in alphanumeric sequence.
Go the to the "Data" tab, and in the "Filter" part of the "Sort and Filter" area, click on "Advanced"
This will bring up a dialogue box:
> Click the radio button for "Copy to another location"
> Make sure the proper range is selected
> Provide a new address for the list to be copied to
> Check the "Unique records only" check box
> Click "OK"

Delete the original list, and copy the unique value list in its place.

 

[dcardno]

Even easier, you could follow the advice here (about four posts down the thread) about removing duplicates:
http://www.mrexcel.com/forum/excel-questions/817363-unique-list.html

I had never even seen that - I have looked past it once a day for several years!

 

[PattiK323]

Thank you so much! I used the filter function daily; but, was not aware of this feature! Awesome!