0

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.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
josh0798
  • 103
  • 6
  • You have tagged vb.net - would a formula solution or VBA be of interest if either is possible? – Tom Sharpe Jun 20 '19 at 10:59
  • That was just a possibility, would rather not use VBA but if that is the only way then so be it. – josh0798 Jun 20 '19 at 11:05
  • 1
    Please show what you have tried so far. – Tom Sharpe Jun 20 '19 at 11:08
  • 1
    Please provide some code. – Chris Jun 20 '19 at 11:10
  • I believe the maths is similar to my answer to this question (triangular matrix) - there might be a simpler way of doing it though https://stackoverflow.com/questions/47922267/how-to-create-all-possible-pair-combinations-without-duplicates-in-google-sheets/47932221#47932221 – Tom Sharpe Jun 20 '19 at 11:14
  • With the formatting corrected, I think this is a pretty good question. Would the downvoters kindly re-evaluate the question as it stands? – L. Scott Johnson Jun 20 '19 at 15:07

3 Answers3

4

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

L. Scott Johnson
  • 4,213
  • 2
  • 17
  • 28
  • Yes perfect answer. I was heading in the same direction, but this is better than my effort would have been. – Tom Sharpe Jun 20 '19 at 17:01
1
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
Mr. Data
  • 71
  • 6
0

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