List w/ Multiple Occurrences, Based on a Variable

[TheRogue]

I have a list of unique items (RED, BLUE, YELLOW, GREEN) & I have an INPUT cell (B1) to specify a number of occurrences (i.e. - 3).
When a value is typed into B1, I need it to generate a new list; repeating each value the specified number of times & then repeating with the next value on the list. In the Given example, I'm looking for a result of:
RED
RED
RED
BLUE
BLUE
BLUE
YELLOW
YELLOW
YELLOW
GREEN
GREEN
GREEN

 

[Akuini]

Try this:
Copy the code then right click the sheet tab > select View Code > paste the code.
The result is in B2 downward.

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)

[COLOR=Royalblue]If[/COLOR] Target.Cells.CountLarge <> [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"B1"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]Dim[/COLOR] ary, x
        [COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

        ary = Split([COLOR=brown]"RED,BLUE,YELLOW,GREEN"[/COLOR], [COLOR=brown]","[/COLOR])
        
        Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
        x = Target.Value
            [COLOR=Royalblue]If[/COLOR] IsNumeric(x) [COLOR=Royalblue]And[/COLOR] x > [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                Range([COLOR=brown]"B2:B"[/COLOR] & Rows.Count).ClearContents
                z = x * (UBound(ary) + [COLOR=crimson]1[/COLOR])
                [COLOR=Royalblue]ReDim[/COLOR] va([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] z, [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])
                [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] a [COLOR=Royalblue]In[/COLOR] ary
                    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] x
                        j = j + [COLOR=crimson]1[/COLOR]
                        va(j, [COLOR=crimson]1[/COLOR]) = a
                    [COLOR=Royalblue]Next[/COLOR]
                [COLOR=Royalblue]Next[/COLOR]
                Range([COLOR=brown]"B2"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
        
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[TheRogue]

I thank you for your response. I probably should have mentioned that I need a formulaic solution.

 

[Marcelo Branco]

Try

	A​	B​	C​	D​
1​	List​	Repeat​		Result​
2​	RED​	3​		RED​
3​	BLUE​			RED​
4​	YELLOW​			RED​
5​	GREEN​			BLUE​
6​				BLUE​
7​				BLUE​
8​				YELLOW​
9​				YELLOW​
10​				YELLOW​
11​				GREEN​
12​				GREEN​
13​				GREEN​
14​
15​

Formula in D2 copied down
=IF(ROWS(D$2:D2)>B$2*ROWS($A$2:$A$5),"",INDEX($A$2:$A$5,INT((ROWS(D$2:D2)-1)/B$2)+1))

M.

 

[TheRogue]

PERFECT! Thank You!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.