AntBlabby8
Board Regular
- Joined
- Apr 18, 2002
- Messages
- 200
I am not sure if this can be done--or how to do it.
I have a huge download into excel, more than 10,000 rows of data each month. And I have absolutely no control of how the data comes into Excel.
The data created from and then divided into two sources--original projection (Budget), and the Actual sales. However, the data for each comes in on a separate rows, which is what creates the problem I am having. (and again, I have absolutely no control of how I get the data.)
There are about a dozen variables, but of there are four variables that matter: Customer Name, Customer Number, Item Name, Item Number. Each one has an Amount, and a column called Source of Data which includes only Budget or Actual.
What I need to do is create a table that shows the aggregated Budget v. the Actual, if the four attribute variables (Customer Name, Customer Number, Item Name and Item Number) are the same. That is, I am trying to combine all sales for each customer/item for the period, first as the budget and then the Actual.
Then--and here's where I can't figure out how to do it, I want to create a VARIANCE column.
I inserted two columns in the data's worksheet one called BUDGET, another called ACTUAL. Then I did an IF function in both columns, referencing the Source of Data Column, so that the amount gets put into the new column as appropriate (e.g., in the Budget the IF pulls in the number from the Amount column, if the Source for that row is Budget, otherwise it enters Zero).
That makes a great Pivot Table that has as columns Budget and Actual to compare. But what I can't figure out how to get is a Pivot Table that will show the Variance: which is what I am aiming for. Because there are multiple entries, I can't do a VLookup or I'll just get one piece of the data.
Can anyone think of how to do this, especially inside a Pivot?
THANKS
I have a huge download into excel, more than 10,000 rows of data each month. And I have absolutely no control of how the data comes into Excel.
The data created from and then divided into two sources--original projection (Budget), and the Actual sales. However, the data for each comes in on a separate rows, which is what creates the problem I am having. (and again, I have absolutely no control of how I get the data.)
There are about a dozen variables, but of there are four variables that matter: Customer Name, Customer Number, Item Name, Item Number. Each one has an Amount, and a column called Source of Data which includes only Budget or Actual.
What I need to do is create a table that shows the aggregated Budget v. the Actual, if the four attribute variables (Customer Name, Customer Number, Item Name and Item Number) are the same. That is, I am trying to combine all sales for each customer/item for the period, first as the budget and then the Actual.
Then--and here's where I can't figure out how to do it, I want to create a VARIANCE column.
I inserted two columns in the data's worksheet one called BUDGET, another called ACTUAL. Then I did an IF function in both columns, referencing the Source of Data Column, so that the amount gets put into the new column as appropriate (e.g., in the Budget the IF pulls in the number from the Amount column, if the Source for that row is Budget, otherwise it enters Zero).
That makes a great Pivot Table that has as columns Budget and Actual to compare. But what I can't figure out how to get is a Pivot Table that will show the Variance: which is what I am aiming for. Because there are multiple entries, I can't do a VLookup or I'll just get one piece of the data.
Can anyone think of how to do this, especially inside a Pivot?
THANKS
