Repeat a value or number in a sequence multiple times.

[bevonclarke]

I have a list say: A,B,C,D,F (IN COLUMN A)
I want the user to select a member of the above list in column B1 and say how many times it must be repeated in column C1. The extracted list can now follow in any other column (a multiple of times say 2)
Example.
User selected A
Repeat 3
List 2 times

The desired output would then be:
A
A
A
B
C
D
E
A
A
A
B
C
D
E

blanks further on.

Please and thank you in advance.
Bevon Clarke

 

[MickG]

Try this for results in column "F".

[COLOR="Navy"]Sub[/COLOR] MG31Jul14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] Ac = 1 To 2
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = [B1] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] n = 1 To [C1]
            c = c + 1
            Cells(c, "F") = Dn.Value
         [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]Else[/COLOR]
         c = c + 1
         Cells(c, "F") = Dn.Value
     [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Ac

Regards Mick

 

[bevonclarke]

Is there maybe some way without VBA...
THANKX

 

[Marcelo Branco]

Without VBA we need helper columns. Maybe something like this


[TABLE="class: grid"]
<tbody>[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]
[TD="bgcolor: #DCE6F1"]

E​

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

F​

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

G​

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

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Select​

[/TD]
[TD]

Repeat​

[/TD]
[TD]

Repeat List​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

List​

[/TD]
[TD]

Repeat​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD]

B​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD]

C​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

B​

[/TD]
[TD][/TD]
[TD]

D​

[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD]

E​

[/TD]
[TD]

1​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

D​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

E​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

B​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

D​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

E​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


List in F2:F6

Formula in G2 copied down
=IF(A$2=F2,B$2,1)

Formula in H1 copied down
=SUM(G$1:G1)

Formula in D2
=IF(C$2*H$6>=ROWS(D$2:D2),INDEX(F$2:F$6,MATCH(MOD(ROWS(D$2:D2)-1,SUM($G$2:$G$6)),H$1:H$6)),"")

A little complicated, but it works

Hope this helps

M.

 

[bevonclarke]

Thank you. Will try it. But it looks fine.

 

[Marcelo Branco]

You are welcome.

M.