How to autogenerate certain excel strings from cer

Question

Basically I want the following in excel:

I need that, if I Input following in Sheet 1:

      A       B       C
1   Name    Size    Color
2   Shirt   S,M,L   Red, Green
3   Trouser 32,34   White, Black, Grey

The following output automatically generates in Sheet 2:

     A                  B     C
1   Name               Size Color
2   Shirt-S-Red         S   Red
3   Shirt-M-Red         M   Red
4   Shirt-L-Red         L   Red
5   Shirt-S-Green       S   Green
6   Shirt-M-Green       M   Green
7   Shirt-L-Green       L   Green
8   Trouser-32-White    32  White
9   Trouser-34-White    34  White
10  Trouser-32-Black    32  Black
11  Trouser-34-Black    34  Black
12  Trouser-32-Grey     32  Grey
13  Trouser-34-Grey     34  Grey

Basically, the all the child rows should be generated from the parent rows, like rows 2 to 7 of Sheet 2 have been generated from Row 2 of Sheet 1, and so on.

Further elaborating, Need such a kind of formula that if I add an another row in Sheet 1 with three Sizes and three Colors of Waistcoat, then the formula or the script should automatically insert further 9 rows to Sheet 2 after Row 13 (i.e., with 9 Permutations and Combinations of Input in Row 4 of Sheet 1).

How can I achieve this using Excel formulas or VB Script? Please tell me the solution.

Thanks.

P.S.: I have tried my best to describe this problem of mine. Please comment, if something is ambiguous in understanding the problem, I shall then further elaborate on that point.

Answer 1

This can be down but I needed to use a small helper table to keep the counts of the number of size items in each row, the number of colours in each row, and finally a running total of the possible combinations for each row.

Proof of Concept

Caveats

Yes, there is more than one!

• Sizes must remain consistant in number of characters. 7,8,9 or 10,11,12 is ok but 8,9,10 will not work. Work around is to use leading 0s.
• Colours needs to be one continuous separated by a space. Comas will be removed. Red, Bright-Red Infra_Red are ok, but Blood Red is not.
• You will need to adjust your ranges to suit your data.

Based on the data in the proof of concept image above, use these formulas in row 3 and copy down.

Column F:

=(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)

Column G:

=(LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)

The above two formulas count the number of comas. Based on that number they determine the number of items in show for the row, and the number of different colours for the row.

Column H:

=F3*G3+H2

This keeps a running total of the number of combinations for each row.

Column J:

=INDEX($A$2:$A$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1))&"-"&K3&"-"&L3

This pulls the name from the list in A2:A4 advancing the row as the formula is dragged down based on the running total of combinations. It then concatenates the string with "-" and text from adjacent columns.

Column K:

=MID(INDEX($B$2:$B$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1)),MOD((ROW(A2)-ROW($A$2)-IFERROR(INDEX($H$3:$H$5,MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)),0)),INDEX($F$3:$F$5,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1)))*FIND(",",INDEX($B$2:$B$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1)))+1,FIND(",",INDEX($B$2:$B$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1)))-1)

Where to begin with that bad boy above...Lets just say I broke it out into sever columns first and then back substitute those formulas as required until I got this. It basically pulls out the size letters in sequence and then starts again at the next colour. May have been simpler to make it dependent on the colour name change in the adjacent column. This formula is independent. of the colour name column.

Column L:

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(INDEX($C$2:$C$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1))," ",REPT(" ",LEN(INDEX($C$2:$C$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1))))),((QUOTIENT(ROWS($A$2:A2)-1-IFERROR(INDEX($H$3:$H$5,MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)),0),(INDEX($H$3:$H$5,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1))-IFERROR(INDEX($H$3:$H$5,MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)),0))/INDEX($G$3:$G$5,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1))))*LEN(INDEX($C$2:$C$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1))))+1,LEN(INDEX($C$2:$C$4,IFERROR(MATCH(ROW(A2)-ROW($A$2),$H$3:$H$5,1)+1,1))))),",","")

Similar to the previous formula this was developed over several columns and then brought back into one cell. It blows apart the colour names, and then grabs each name in sequence after the number of rows equal to the number of sizes has past.