I have a large table containing daily inputs into a system, which has the columns as shown:
Day Itemname1 Itemquant1 Itemname2 Itemquant2 …..
The item names and quantities are independent of each other. On one day, one might receive 10 Item A, and on another day, one might receive 5 of Item D, 6 of Item B, and 234 of Item C. In the former case, only 2 colums will have data, and in the latter case, 6 columns will have data. All the other columns will be empty. An example:
Day N1 Q1 N2 Q2 N3 Q3 1 A 10 2 D 5 B 6 C 234
How would a pivot table be used to display, say, the quantities of A daily over a year, or the number of non-zero values of A in a year? Note that A may appear in multiple columns, and there are hundreds of possible items, so simply creating a table with each variable in a column is not feasible.
I have tried adding all of the columns in multiple ways, but it ends up returning the values of A, given all the values of B, given all the values of C, which is useful if the values are dependent, but clutters up the page since they are uncorrelated.
If this is not possible, what would be a good method to obtain the information required from the spreadsheet?