Itemized Output Table from Batch Input Table

[eibi]

Friends --

Take a look at the attached sample spreadsheet. Given a set of input keyed in the grey boxes, I am looking for a drag-to-fill formula for the orange cells.

In the Itemized Output (orange cells), each item repeats as many times as indicated in the corresponding grey box.

The final application will be an input table approximately 50 rows and 3 columns. CSE/array formulas are acceptable. I prefer to avoid VBA for this problem.

Even if you don't have a full solution - - welcome even partial suggestions.

 

[Hui]

It is called Unpivotting

You should be able to use the technique discussed here:
http://chandoo.org/wp/2016/03/08/unpivot-and-pivot-pq/

 

[Chihiro]

Alternate method if you don't have PowerQuery.

http://datapigtechnologies.com/blog/index.php/transposing-a-dataset-with-a-pivottable/

 

[bosco_yip]

Formula way.

1] In I3, formula copy down :

=LOOKUP(ROWS(A$1:A1),SUMIF(OFFSET(A$3:C$3,,,ROW($1:$4),),"<>")+1,D$4:D$6)&""

2] In J3, CSE-formula copy down :

=IF(I3="","",LOOKUP(COUNTIF(I$3:I3,I3)-1,IFERROR(SUMIF(OFFSET(INDEX(A$4:A$6,MATCH(I3,D$4:D$6,0)),,,,ROW($1:$3)-1),"<>"),0),T(OFFSET(A$3,0,{0;1;2;0;1;2;0;1;2}))&""))

Regards
Bosco

 

[eibi]

Beautiful, Bosco ! -- Exactly the guidance I needed.

Hui, Chihiro:

I appreciate your suggestions, but Pivot Tables and Power Query are hard for me to adapt to a format I can easily share with and explain to co-workers. (I always end up getting called to their desk: "How did you do that again?")

This is an ongoing challenge: how to transition slightly more sophisticated data crunching methods to repeatable, error-resistant processes that can be operated by all users.

Always appreciate the ideas. Chandoo's forums are a very valuable resource to me and my bosses (whether they realize it or not...)