Brainfart: Excel question

Freefaller

Freefaller

Freefaller

Man of Honour
Joined
5 Jun 2003
Posts
91,544
Location
Falling...
Hi all,

Just having a complete brainfart today and cannot think of how to do something - this is not work related, this is for some personal curiosity.

Let's say I have 10 separate data sets over a period of 5 years (i.e. 2 per year) - but that measure the same things, i.e. red, green, late, early, night, day (there's about 40 of these), but their values vary over the 10 pages I have, each page is 6 months apart measuring the same things, but different results.

I'd like to compare all 10 data sets and how things have changed over time / trending.

My first thought was to have 10 sheets within excel with the same details just changing the values accordingly - but that doesn't seem that sophisticated. I also thought about just having a column with the dates and their corresponding values next to it. The 40 rows will never change and I'll just add columns as I get more data. I'm sure there must be a simpler way of doing it.

I know I'm going to kick myself when someone suggests a solution
 
Last edited:
If it's only updated once every 6 months then what you've described is probably the best value for effort. There's no point in spending loads of time doing something more fancy from the sounds of things.
 
Last edited:
I'm not sure if you can create the equivalent of a cartesian join for the date range for one axis of a pivot table, so a table of days, months and years that covers at least the period the actual data covers, would create all the permutations of dates. Once you have a table / view of those you can join from that to the actual data.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom