Excel or OpenOffice Table Summary: how to reconstruct a table from another, with "missing" values
- by Gilberto
I have a table of values (partial) with 3 columns: month (from 1 to 12), code and value. E.g.,
MONTH | CODE | VALUE
1 | aaa | 111
1 | bbb | 222
1 | ccc | 333
2 | aaa | 1111
2 | ccc | 2222
The codes are clients and the values are sales volumes. Each row represents the sales for one month for one client. So I have three clients, namely aaa, bbb, and ccc. For month=1 their sales volumes are: aaa-111, bbb-222, and ccc-333. A client may or may not have sales for every month; for example, for the month 2, the client bbb has no sales. I have to construct a completed summary table for all the MONTH / CODE pairs with their corresponding VALUE (using the value from the "partial" table, if present, otherwise print a string "missing").
MONTH | CODE | VALUE
1 | aaa | 111
1 | bbb | 222
1 | ccc | 333
2 | aaa | 1111
2 | bbb | missing
2 | ccc | 2222
Or, to put it another way, the table is a linear representation of a matrix:
and I want to identify the cells for which no value was provided.
How can I do that?
