0

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?

Improve this question
6
  • I found a solution. I hope it may be useful for you. I create another 2 tables (the partial table and the summary table) with the following columns: Concatenate(MONTH;".";CODE) | VALUE And finally I made a VLOOKUP between the values of the tables.
    – Gilberto
    Oct 30, 2013 at 7:01
  • In the question it is not clear why the months repeat (they are in different years?). The description of the reconstruction is not very clear too. You mean that every pair of MONTH and CODE values should have the VALUE value assigned if it exist or missing if does not? Please add your reply when you will be allowed to do so (there is a time limitation for low-reputation users). Thank you.
    – pabouk - Ukraine stay strong
    Oct 30, 2013 at 7:37
  • The code are the clients and the values are the sales volume. The "partial" table contains rows; each row is the sale volume for that month for that client. So for month=1 I have three clients each one has the following month sale volume: aaa -> 111,bbb ->222,ccc->333. A client may has or not a sale volume for every month; for example, for the month 2 the client bbb has not volume. The summary table contains every month for every client: if there's no sale volume, the associated value must be "missing".
    – Gilberto
    Oct 30, 2013 at 13:01
  • I solve the problem creating another table with only 2 columns: the first is the concatenation of month and value, for example "1.aaa","1.bbb", ... , "2.ccc". I made that table for the partial and for the summary (this one has not the values <- I have to compose them). Finally I apply a VLOOKUP between the two tables, comparing row by row with the formula "=IF(ISERROR(VLOOKUP(G15;$G$4:$H$12;2;0));"MISSING";VLOOKUP(G15;$G$4:$H$12;2;0))" It will be more simple to explain if I could upload an example. I hope you understand despite my bad english.
    – Gilberto
    Oct 30, 2013 at 13:05
  • Thank you for the explanation. Adding the relevant information into the original question is certainly more useful for other users than leaving it in comments. You will be allowed to self-reply your question after the time limit which is 8 hours after the question was posed if I remember correctly. You can format the proper reply the same way as the question so you can add examples there.
    – pabouk - Ukraine stay strong
    Oct 30, 2013 at 13:20

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .