N'Al on 15/4/2008 at 21:47

---

I was hoping some of you more knowledgable types might be able to help me with my questions about MS Excel. The problem I have is this:

I've currently got a spreadsheet with multiple pivot tables, each one of which contains sales volume information for the month. One pivot table shows actual sales data, the next budgeted sales data, the next forecasted sales data, and so on. Each of the pivot tables itself is split to show the sales information by customer and by product, so the layout of the tables looks a bit like this:


PERIOD 4

[indent]ACTUAL 08[/indent]

[indent][indent][indent][indent]Product-X Product-Y TOTAL[/indent][/indent][/indent][/indent]
[indent]Customer A[/indent]
[indent]Customer B[/indent]
[indent]Customer C[/indent]
[indent]Customer D[/indent]
[indent]Customer E[/indent]




[indent]BUDGET 08[/indent]

[indent][indent][indent][indent]Product-X Product-Y TOTAL[/indent][/indent][/indent][/indent]
[indent]Customer A[/indent]
[indent]Customer B[/indent]
[indent]Customer C[/indent]
[indent]Customer D[/indent]
[indent]Customer F[/indent]




[indent]FORECAST 08[/indent]

[indent][indent][indent][indent]Product-X Product-Y TOTAL[/indent][/indent][/indent][/indent]
[indent]Customer A[/indent]
[indent]Customer B[/indent]
[indent]Customer C[/indent]
[indent]Customer D[/indent]
[indent]Customer F[/indent]
[indent]Customer G[/indent]
[indent]Customer H[/indent]



What I now want to do is to compare the sales information in one pivot table to the information in another, so e.g. actual data vs budget data. What makes this difficult for me is the fact that some customers may only be present in one of the pivot tables and not the others (which I've tried to highlight by showing those customers in different colours), and this works both ways - a budgeted customer my not have any actual data, whereas equally an actual customer may never have been budgeted in the first place.

I've come up with 2 ideas about how I could do this, but I am not sure whether they are feasible or how to go about implementing them (i.e. the tables/ formulas/ macros/ etc. involved).



* Another pivot table that would use two of the already existing pivot tables as sources (e.g. actual and budget data) and be set to display the difference between them. This would be some sort of 'merged' pivot table, I guess, and it should - if working as imagined - have no problems with odd customers. I am not sure how I could create such a 'merged' table, though. Excel seems to be pretty inflexible regarding using pivot table output as input for another table.


* The currently existing pivot tables all receive their information from seperate sheets, i.e. there is one for actual data, one for budget data, etc. This is because, for example, the budget sheet includes data for all reporting periods, not just the current one. So far, it is only the frontsheet (that I've tried to draw above) that consolidates data from the different report types.

I'm thinking I could, though, create another sheet that draws the relevant total actual, budgeted and forecast data for the period together, and I could then base a further pivot table off of that sheet. So, for example, column A in the sheet would have the customers' names, column B total actual sales value, column C total budgeted sales value, etc. The pivot table would then simply compare columns B and C for each customer.

Since we could have new customers any time, though, it'd be great for column A to populate itself automatically, so to speak. Is there some sort of formula or macro I can use to merge the customer names in the existing pivot tables, whilst automatically getting rid of any duplicate entries? Essentially, something that would result in the following list in column A, and would also automatically add any new customer that may crop up in the future?


[indent]Customer A[/indent]
[indent]Customer B[/indent]
[indent]Customer C[/indent]
[indent]Customer D[/indent]
[indent]Customer E[/indent]
[indent]Customer F[/indent]
[indent]Customer G[/indent]
[indent]Customer H[/indent]


I hope this post is clear enough, but if not just let me know what's unclear and I'll try and explain in more detail.

Help will be very much appreciated, thanks!

N'Al on 17/4/2008 at 20:44

---

Thanks d0om, but I'm not quite sure KNIME is the right thing for what I'm trying to do.

I've had another search and I've now come across (http://support.microsoft.com/kb/291320/EN-US/) this; the section under Sample 2 seems to suggest that it's possible to write a macro that would analyse and merge lists, as well as deleting any duplicate entries, pretty much what I'm trying to do. Unfortunately, I only understand half of the code; plus, I'm trying to merge more than just 2 lists, so I'm not even sure whether (and how) the macro in the link could be extended to do that.

Hrm, looks like I have no choice but to properly get to grips with Excel macros and VBA... :erg: