Today we are going to discuss about numerous methods in Excel to generate sequence of numbers. Our solution going to involve Excel formula and M-Code. Lot to learn, let's dive in.
Our requirement is to generate sequence of numbers which will follow a pattern that 2 will get repeated twice and 3 will get repeated thrice and so on. Our input in B3 cell decides the end of sequence.
Method-1:
Office 365 Version - Single Cell Formula,
Formula in D3 cell,
=LET(y,SEQUENCE(B3),TEXTSPLIT(CONCAT(REPT(y&" ",y)),," ",1))
Let me start this from scratch,
=SEQUENCE(B3) will deliver the sequence of numbers from 1 to 5
={1;2;3;4;5}
This will stored in the variable 'y'.
=REPT({"1 ";"2 ";"3 ";"4 ";"5 ";"6 ";"7 "},{1;2;3;4;5;6;7})
This will help us to repeat the numbers based on the count of same number, output will look like this,
={"1 ";"2 2 ";"3 3 3 ";"4 4 4 4 ";"5 5 5 5 5 ";"6 6 6 6 6 6 ";"7 7 7 7 7 7 7 "}
Each numbers getting repeated based on the same number count with space as delimiter.
Space character will help us to split each number and to form the sequence.
CONCAT function concatenate text array into single text,
=1 2 2 3 3 3 4 4 4 4 5 5 5 5 5 6 6 6 6 6 6 7 7 7 7 7 7 7
TEXTSPLIT function will split this based on the Space delimiter and 1 in ignore_empty argument will ignore blank cells.
Method-2:
=INT((1+(8*SEQUENCE(SUM(SEQUENCE(B3))))^0.5)/2)
Let me start this from scratch,
=SUM(SEQUENCE(B3))
This will create sequence of numbers {1;2;3;4;5} and will sum them. So output is 15.
SEQUENCE(15), this will give us {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}
Multiply this array with 8,
{8;16;24;32;40;48;56;64;72;80;88;96;104;112;120}
(^0.5) is the replacement of Square root,
{2.8284;4;4.899;5.6569;6.3246;6.9282;7.4833;8;8.4853;8.9443;9.3808;9.798;10.198;10.583;10.9545}
This will give us the extraneous decimals, but I took only 4 decimals to display here.
Add 1 to this array and then divide it by 2. If we pick integer part of return array, we can see our desired result.
Method-3:
LAMBDA version Office 365,
=DROP(REDUCE(0,SEQUENCE(B3),LAMBDA(a,v,VSTACK(a,SEQUENCE(v,,v,0)))),1)
This lambda function iterates in sequence of {1;2;3;4;5]
in 2nd iteration,
it generates the sequence for 2 rows, which starts from 2 and increments 0. So this iteration will deliver us {2;2}
in 3rd iteration,
it generates the sequence for 3 rows, which starts from 3 and increments 0. So this iteration will deliver us {3;3;3}.
It does the same computation for 5 iteration and stacks each result vertically.
Method-4:
=LET(a,B3,x,SEQUENCE(a),y,SEQUENCE(,a),TOCOL(IFS(x>=y,x),3))
Here, I gave you clear picture to understand the process, We can convert this 5*5 array into one column using TOCOL function and we can ignore errors by selecting 3rd option in ignore argument.
Method-5:
Power Query solution,
let
N = 5,
Source = {1..N},
Result = List.Combine( List.Transform( Source, (f) => List.Numbers(f,f,0)) )
in
Result
Method-6:
Power Query solution,
let
N = 7,
Source = {1..N},
Result = List.Combine( List.Transform(Source, (f) => List.Repeat({f},f)) )
in
Result
Method-7:
Power Query solution,
let
N = 7,
Source = {1..N},
Result = List.Combine( List.Transform( Source, (f) => List.Generate(
() => [a = f, i = 1], each [i] <= f, each [a = [a] + 0, i = [i] + 1],each [a])))
in
Result
Comments