Let's determine to explain the problem that I have products that do not fit into one box. I need to mark every box. If the product fits up to 1 box, then in column C I only have the name of the product and the column D is empty. However, if the product needs to be packed into 4 boxes, then 4 rows with the product name should appear in column C and in column D should appear box no. / number of boxes.
I have in Google Sheet table as below:
| [A] ITEM | [B] BOXES | [C] ITEM TO PACK | [D] BOXES USED |
|---|---|---|---|
| ITEM A | 1 | ITEM A | |
| ITEM B | 1 | ITEM B | |
| ITEM C | 2 | ITEM C | 1/2 |
| ITEM D | 1 | ITEM C | 2/2 |
| ITEM E | 3 | ITEM D | |
| ITEM C | 2 | ITEM E | 1/3 |
| ITEM E | 2/3 | ||
| ITEM E | 3/3 | ||
| ITEM C | 1/2 | ||
| ITEM C | 2/2 |
If it is easier, then of course we can give 1/1 in column [D] BOXES USED for product that fit up to 1 box.
I tried to do it with one formula - too difficult. I tried to do it with two formulas, but still too difficult. I manage to multiply the number of products by the number of boxes to use, just as with the number of boxes themselves, but I can't add these 1/4, 2/4, 3/4, 4/4.
To get a result in [C] ITEM TO PACK I use formula:
=INDEX(LEFT(FLATTEN(TRIM(SPLIT(QUERY(REPT(A:A & "×", B:B),, 9^9),"×"))),6))
I can use only formulas, no Apps Script.
I can see that you were able to create your desired result for column c. What I have focused on is creating your D Column. I have used reduce function to iterate through the column B and process it.
To create the sequence of Data:
=REDUCE("[D] BOXES USED",B2:B7, LAMBDA(a,c, VSTACK(a,BYROW(SEQUENCE(c,1,1,1),LAMBDA(r,CONCATENATE(r,"/",c))))))
This is the Formula for attaching it to your current formula and getting your whole expected result.
=IFNA(HSTACK(VSTACK("[C] ITEM TO PACK",INDEX(LEFT(FLATTEN(TRIM(SPLIT(QUERY(REPT(A2:A & "×", B2:B),, 9^9),"×"))),6))),REDUCE("[D] BOXES USED",B2:B7, LAMBDA(a,c, VSTACK(a,BYROW(SEQUENCE(c,1,1,1),LAMBDA(r,CONCATENATE(r,"/",c))))))),"")