0
I have an Excel spreadsheet with several years of daily data in the following form:
Date | Category1 | Category 2 | ... | Category30 ------------------------------------------------------------ dd/mm/yyyy | value | value | ... | value ... | ... | ... | ... | ... ------------------------------------------------------------
I also have a list in the following form, where each of the categories from the first table is assigned one of five Type descriptions:
| Type | Category | ---------------------- | Type1 | Category1 | | Type2 | Category2 | | Type2 | Category3 | | ... | ... | | Type5 | Category30 | ----------------------
I want to be able to organize and summarize these data using a Pivot Table, and plot the time series using a Pivot Chart. However, because of the way the data are tabulated I cannot group them by category, type or sequential month (I am interested in charting the data by month/year instead of grouping them by only months across years).
If I ordered the data like in the following table, all my problems would be solved, but I would be duplicating several column's values as many times as there are categories (and I am very much opposed to duplicating data):
Date | Year | Month | Day | Type | Category | Values -------------------------------------------------------------------------- date(i) | yyyy | mmm | dd | Type(j) | Category(k) | value(i,j,k) --------------------------------------------------------------------------
My question is, how should I design my table so that I can quickly handle the data with pivot tables without having to duplicate all the other information?
2
+1, great answer @mtone. Your last comment about the source data being a good pivot result is spot on. @Ricardo, you said you don't want to duplicate data but for pivot tables to work it is necessary. You get greater flexibility to manipulate your data at the expense of duplicating a lot of it. You can find instructions to unpivot your source data here
– Mike Fitzpatrick – 2011-04-27T22:42:13.667I agree that my source data would actually be a good result of the Pivot Table! That, along with my inability to come up with a simple way to group the data in a meaningful way made me consider the benefits of creating a proper database. I'm currently halfway through rearranging the data and so far (except for the unavoidably large number of rows) things are looking good. Thanks! – Ricardo – 2011-04-28T13:31:23.587