From Matrix to Single List

[gnulab]

Hi Guys,

I would like to ask for pointers how to solve this. I have a table is as follows:

	SMALL	MEDIUM	LARGE	x-large
design a
design b
design c
design d

Firstly, I would like to convert the products offered from matrix to single list format, that is all the various combinations in one single list: such as this.

design a - small
design a - medium
design a - large
design a - x-large
design b - small
design b - medium
and so on.

Afterwards, I would like for the value at each intersection to be copied to the corresponding entry in the single column.


How do I accomplish this?


Thank you!
Henry

[samba_ravi]

Attach a sample excel file

[Richard Buttrey]

If this is a one off request then I think I'd take a pragmatic approach.
First copy all the design names and repeatedly paste them to the next blank row in column A for as many times as there are sizes less 1. In this example 4 sizes so copy the names 3 times to A6, A10, A14

Now copy each of the columns B2:B5, C2:C5, D2:D5..etc to B6, B10, B14

Finally filter B for blanks and delete these rows.

[Glenn Kennedy]

...or with 3 formulae:

Design column:
=OFFSET($A$2,INT((ROWS($1:1)-1)/4),,,)

Size column:
=OFFSET($B$1,,MOD((ROWS($1:1)-1),4),,)

Values column:
=OFFSET($B$2,INT((ROWS($1:1)-1)/4),MOD((ROWS($1:1)-1),4),,)

in all cases, copy down. See attached Excel sheet...

[FlameRetired]

Another way.

In M6 and filled down until you get blanks ... @ row 929.

=LOOKUP("zzzzzzzz",CHOOSE({1,2},"",INDEX($C$6:$C$137,CEILING(ROWS($1:1)/COUNTA($E$5:$K$5),1))&"-"&INDEX($E$5:$K$5,MOD(ROWS($1:1),-COUNTA($E$5:$K$5))+COUNTA($E$5:$K$5))))

In N6 and copied down until you get blanks.

=IF(ISERROR(INDEX($E$6:$K$137,CEILING(ROWS($1:1)/COUNTA($E$5:$K$5),1),MATCH(RIGHT(M6,3),$E$5:$K$5,0))),"",INDEX($E$6:$K$137,CEILING(ROWS($1:1)/COUNTA($E$5:$K$5),1),MATCH(RIGHT(M6,3),$E$5:$K$5,0)))

Column Q is your original sample output.