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

2019-06-20 excel vba

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.

``````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
``````

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) )

``````=B1*10+ROW()