• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Challenge ex.2 Repeat series of numbers

excelent

New Member
ok folks time for a new challenge

A B C
1 3 10 10
2 20 20
3 30 30
4 40 40
5 50 50
6 10
7 20
8 30
9 40
10 50
11 10
12 20
13 30
14 40
15 50
16

Construct a formula to repeat the numbers in column B 3 times (A1)
If u put a 5 or 10 or what ever in A1, the numbers should repeat that many times
Same as what ever u put in Column B. Numbers, letters as many u like.. repeat them in C.
 

excelent

New Member
hmm try Again

......A....B....C
1... 3...10...10
2.........20...20
3.........30...30
4.........40...40
5.........50...50
6...............10
7...............20
8...............30
9...............40
10.............50
11.............10
12.............20
13.............30
14.............40
15.............50
16
 

excelent

New Member
Hi John thanks for your reply an formula.
Working just fine as expected, is a bit longer than mine but WHO is counting right :)

I gues no one else is trying- maby it too hard ;-)

mine :
=IF(ROW(1:1)<COUNTA(B:B)*$A$1+1,INDEX(B:B,MOD(ROW(1:1)-1,COUNTA(B:B))+1),"")
 

John Jairo V

Well-Known Member
Hi!

Another 2 solutions:

=IF(ROWS(C$1:C1)>$A$1*COUNTA(B:B),"",OFFSET($B$1,MOD(ROWS(C$1:C1)-1,COUNTA(B:B)),))
=IF(ROWS(C$1:C1)>$A$1*COUNTA(B:B),"",INDIRECT("B"&MOD(ROWS(C$1:C1)-1,COUNTA(B:B))+1))


This challenge have many ways to do it, but INDEX is a nice way because is not volatile (OFFSET and INDIRECT are volatile).

Another thing to consider: You can use ROW(A1), or ROW() instead of ROW(1:1) in this problem, but all of these solutions have a problem: if you insert a row above to row 1 the formulae does not work, while ROWS(C$1:C1) does.

Blessings!
 
Top