Combining a dynamic table with a static table into one pivot

[bjornh]

Hi,

I have an hopefully interesting request for someone familiar with data-merging in Excel 2013.

I just got my first job and I'm trying to create some new user-friendly spreadsheets for them to use.

The data I am working with is input from customers on their estimated demand for our products in the next 12 months.

So I have an odbc link (which I know very little of) that brings up a ~6000 row data set into a table upon clicking "Refresh All". This data set changes in row numbers every time a change is made to the external database (which happens all the time).

This dynamic table only brings up the estimates from current month and 12 months forward (i.e. apr 2015 to apr 2016). I would like to "save" these estimates at the end of each month and be able to compare what the customers tell us from month to month.

I have started to build a seperate static data table where I just copy in the values at the end of each month and add a column saying when the data was forecasted. This works great for comparing data for past months, but I can't find a good way to also bring in the current (dynamic) data in the pivot.

I've looked into table connections, but none of my columns in the raw imported data contains unique values. The only unique combination is "Customer name+Product name+Demanded in month" combined.

There may be other easier way of working with this static and dynamic data together. Maybe something as simple as having the dynamic table in the row below the static data or something like this. I'm pretty new to Excel and struggling a lot to figure this out.

I've attached a sample file that should illustrate the issue. I would be eternally grateful if someone could help me out.

https://www.sendspace.com/file/getb3g (sorry if the download site is spammy)

Thanks in advance!

 

[ImkeF]

Hi bjornh,
what do you actually want to compare?:
The current month values spanning from apr2015-apr2016 against previous month values, ranging from mar2015-mar2015 (so each first month of the prognosis against an old prognosis) or do you want to Show how the forcast for Apr2015 has changed the last 12 months?

Basically: How shalll your x-axis look like: Months (Jan-Dec) or estimation intervalls (1-12)

(Don't dare to open that link...)

 

[bjornh]

Thanks a lot for your quick reply!

I think I mean your first suggestion, where you see the next 12 months on the X-axis and the different forecasts as different bars.

Here's (hopefully) a safe photo

 

[bjornh]

I attached photos of the static table I'm building and the dynamic table if it is of any interest. Thanks a bunch!

 

[ImkeF]

You can "automate" this by using Power Query (do you run Office Professional?)

I'd merge all the data in one table: Creating an additional column for the current/dynamic values corresponding to your "Forecasted in Month", automatically filled with a date from the current month. (Append-Operation in PowerQuery, UNION in SQL&Access): Just hang the current data at the end of your Historic Data table.

You'd then just have an additional month with your current values in table above where your chart is based upon.

If you don't have PQ, and don't want to copy paste your figures manually in Excel, you could also use ACCESS (UNION-Query), but it's so slow compared to Power Query...

Imke