create a list based on changing criteria

[cddcin2]

I need a formula that will create a list based on the quantity entered. My problem is having the list generate based on the quantity changing. I need it to be a single list. I've tried a number of possibilities, but the formulas seem to get too complicated after the first few cells and I can't account for all of the possible scenarios that could be entered. Any help or guidance would be greatly appreciated. In the example below the only field that would change is the "# of people". The list of "Favorite Color" would not change:

	A	B	C	D	E	F
1	Favorite Color	Blue	Red	Green	Yellow	Orange
2	# of people	2	1	3	2	3

<tbody>
</tbody>

The desired result would be:

Blue

Blue

Red

Green

Green

Green

Yellow

Yellow

Orange

Orange

Orange

<tbody>
</tbody>

 

[Matty]

Welcome to MrExcel!

In A5, try:

=IF(ROWS(A$5:A5)>SUM(B$2:F$2),"",INDEX(B$1:F$1,MATCH(TRUE,MMULT((TRANSPOSE(COLUMN(B$1:F$1))>=COLUMN(B$1:F$1))+0,TRANSPOSE(B$2:F$2))>=ROWS(A$5:A5),0)))

Array entered, i.e. with CTRL+SHIFT+ENTER, before copying down.

Change ranges to suit.

Matty

 

[cddcin2]

Thank you Matty! I was not familiar with MMULT...just read up on it and I am now. I appreciate the answer and the lesson. Cheers.

 

[Matty]

Glad I could help.

Matty