• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Itemized Output Table from Batch Input Table

eibi

Active Member
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.
 

Attachments

  • eibi_sample.xlsx
    10.1 KB · Views: 17
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
 

Attachments

  • eibi_sample (1).xlsx
    11.8 KB · Views: 17
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...)
 
Last edited:
Back
Top