Hello!
There are two input tables. Input 1 is a schema (e.g. what can be bought) which is followed by entry table in input 2 (e.g. what was bought).
How to read
If key is Bob, then there are 3 variables named apple, pear and orange. Now using the schema Bob, we placed an order for 20 apples, (blank) pears and 50 oranges in country EU and city Amsterdam. As the Bob schema has only 3 variables, the fourth column value_6 is not applicable.
Idea
01 I can use VBA to add value_i behind name column in input 1, something like if order_t <> order_t-1, then value_i and i=i+1, "". Now I can attribute value to variable name.
02 Go from right to left in input 2 table and find the first non-empty value. Then number of columns till the end of table minus 2 to get a number of rows for each input 2 entry.
03 Hardcode names behind value_1 and value_2 which are static
04 Not sure how to bring it all together
Looking forward to hearing your suggestions. I prefer simple design.
There are two input tables. Input 1 is a schema (e.g. what can be bought) which is followed by entry table in input 2 (e.g. what was bought).
How to read
If key is Bob, then there are 3 variables named apple, pear and orange. Now using the schema Bob, we placed an order for 20 apples, (blank) pears and 50 oranges in country EU and city Amsterdam. As the Bob schema has only 3 variables, the fourth column value_6 is not applicable.
Idea
01 I can use VBA to add value_i behind name column in input 1, something like if order_t <> order_t-1, then value_i and i=i+1, "". Now I can attribute value to variable name.
02 Go from right to left in input 2 table and find the first non-empty value. Then number of columns till the end of table minus 2 to get a number of rows for each input 2 entry.
03 Hardcode names behind value_1 and value_2 which are static
04 Not sure how to bring it all together
Looking forward to hearing your suggestions. I prefer simple design.
Last edited:
