CODEWITHSUNDEEP
excelvba
josh0798
josh0798

Reputation: 103

How can I create a list of numbers that is like the following?

I want to create a list of number like this in Excel, in separate cells:

(column B);
1
1
2
1
2
3
1
2
3
4
.
.
.
.

If you can't see each number is sequences from one up to the number your on.

Upvotes: 0

Views: 143

Answers (3)

L. Scott Johnson
L. Scott Johnson

Reputation: 4402

Using formulas only, no VBA:

In column B:

1 | 1
2 | 1
3 | =IF(B2-MAX(B$1:B1)<1,B2+1,1)

Then fill that formula down

If you want just one formula that stands on its own (without referencing other cells), you can evaluate the triangle sequence at term n = ROW() with this more imposing formula:

=IFERROR(ROW() - COMBIN(INT((1+SQRT(8*ROW()))/2), 2),1)

(The IFERROR part just handles the first term, which attempts to evaluate COMBIN(1,2) )

Upvotes: 4

user11509084
user11509084

Reputation:

If you start with the number 1 in B1 then put 

=B1*10+ROW()

in B2 you can drag this down

But you don't state what the pattern should be after B9

Upvotes: 0

Mr. Data
Mr. Data

Reputation: 71

x = 1 'the starting row value
y = 2 'the starting column value
z = 9 'the number of entries you ultimately want to make
entryMax = 1 'the max number of this sequence
entryStart = 1 'the start of the sequence

Do While x<=z
entryStart = 1 'reset the value of entryStart    
    Do While entryStart <= entryMax
    cells(x, y) = entryStart
    x= x + 1
    entryStart = entryStart + 1
    Loop
entryMax = entryMax + 1
Loop

Upvotes: 1

Related Questions