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: 338
        
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?
© Super User or respective owner