Excel or OpenOffice Table Summary: how to reconstruct a table from another, with "missing" values

Posted by Gilberto on Super User See other posts from Super User or by Gilberto
Published on 2013-10-30T05:45:04Z Indexed on 2013/10/31 22:00 UTC
Read the original article Hit count: 236

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:

                                matrix with aaa, bbb, and ccc

and I want to identify the cells for which no value was provided.


How can I do that?

© Super User or respective owner

Related posts about microsoft-excel

Related posts about openoffice.org