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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

List​

[/td][td]

Repeat​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

RED​

[/td][td]

3​

[/td][td][/td][td]

RED​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

BLUE​

[/td][td][/td][td][/td][td]

RED​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

YELLOW​

[/td][td][/td][td][/td][td]

RED​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

GREEN​

[/td][td][/td][td][/td][td]

BLUE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td]

BLUE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td]

BLUE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td]

YELLOW​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td]

YELLOW​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td]

YELLOW​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td]

GREEN​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td][/td][td][/td][td]

GREEN​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td][/td][td][/td][td]

GREEN​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.