Combine data to give new list

[Dugmac]

Hi there

I have a list of product codes for which I want to assign to each a "size" category. Can anyone think of a way to automate this process? I've included an example below showing 3 product codes and 5 sizes. I want to create a new list which gives for each item five new product codes (one for each size) so 20 items in total as shown in the Revised Data cloumn.

Thanks very much

Dugmac

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C
1
2
3	3294200100002		3294200100002S
4	3294200100001		3294200100002M
5	3294200100005		3294200100002L
6			3294200100002XL
7			3294200100002XXL
8			3294200100001S
9			3294200100001M
10	S		3294200100001L
11	M		3294200100001XL
12	L		3294200100001XXL
13	XL		3294200100005S
14	XXL		3294200100005M
15			3294200100005L
16			3294200100005XL
17			3294200100005XXL
18
Main

Cell Formulas
Range	Formula
C3:C17	C3	=TOCOL(IF(A10:A14="",x,TRANSPOSE(A3:A5)&A10:A14),3,1)
Dynamic array formulas.

 

[Dugmac]

Thanks very much For some reason it doesn't seem to work (I get #values when I post that formulae into my sheet).

Any idea what I'm doing wrong? I'm probably being thick!

 

[Fluff]

Ok, you probably don't have th TOCOL function yet.
How about

Fluff.xlsm
	A	B	C
1
2
3	3294200100002		3294200100002S
4	3294200100001		3294200100002M
5	3294200100005		3294200100002L
6			3294200100002XL
7			3294200100002XXL
8			3294200100001S
9			3294200100001M
10	S		3294200100001L
11	M		3294200100001XL
12	L		3294200100001XXL
13	XL		3294200100005S
14	XXL		3294200100005M
15			3294200100005L
16			3294200100005XL
17			3294200100005XXL
18
Main

Cell Formulas
Range	Formula
C3:C17	C3	=LET(a,A3:A5,b,A10:A14,r,ROWS(b),s,SEQUENCE(r*ROWS(a),,0),INDEX(a,INT(s/r)+1)&INDEX(b,MOD(s,r)+1))
Dynamic array formulas.

 

[Dugmac]

Thanks again but now I get:

 

[Fluff]

Do you normally use commas as the separator in a formula?

 

[Dugmac]

Sorry I'm not sure I understand the question.

 

[Fluff]

Inside the formula all the different parts are separated by a comma. Is that what you normally use, or do you normally use a semi-colon?

 

[Dugmac]

Inside the formula all the different parts are separated by a comma. Is that what you normally use, or do you normally use a semi-colon?

Ah I see. I would use a comma.

 

[Fluff]

In that case do you have both the LET and SEQUENCE functions?