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) )
If you start with the number 1 in B1 then put
in B2 you can drag this down
But you don't state what the pattern should be after B9
- How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
- How to read and write excel file
- How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
- How to convert a column number (e.g. 127) into an Excel column (e.g. AA)
- Return empty cell from formula in Excel
- Function to convert column number to letter?
- Get content of a cell given the row and column numbers
- How can I perform a reverse string search in Excel without using VBA?
- How to create the column smartly?
- How to create a list from beginning number and end number