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?