Today we are going to discuss about numerous methods to generate sequence of numbers from given grouped numbers. See below picture to get clear idea about the requirement.
This is going to be an epic formula solution. Lot to learn, let's dive in.
Classic Method:
I will share the Classic version formula at the end of this method, to make you understand the process, i used LET function to avoid repeating constructions.
Formula in C2 cell is,
= LET(
A, $A$2:$A$7,
L, LEFT(SUBSTITUTE(A, "-", REPT(" ", 5)), 5),
R, RIGHT(SUBSTITUTE(A, "-", REPT(" ", 5)), 5),
Cnt, R - L + 1,
Rng, L + TRANSPOSE(ROW(INDIRECT("1:" & MAX(Cnt))) - 1),
SMALL(
IF(
Rng > (R * 1),
"",
Rng
),
ROWS(
$D$2:D2
)
)
)
In REPT function we fed 5, so this will be maximum number of digit this formula can handle.
Name L carries left part of the text values before hyphen. If we evaluate this,
={"1 ";"4 ";"9";"10 ";"13 ";"21"}
These are all numbers formatted as text. We are going to do mathematical operations on this text numbers, so excel will convert them into numbers. This will act as the starting number of each sequence.
"9" and "21" don't have hyphens, that's why we used SUBSTITUTE function to avoid errors.
Name R carries right part of the text values after hyphen.
={" 2";" 6";"9";" 11";" 16";"21"}
This will act as the last numbers of each sequence.
Name Cnt carries the count of numbers in each groups,
=Right Part - Left Part + 1
We subtract left side numbers from right side numbers and add 1,
={2;3;1;2;4;1}
Now we are going to add {0,1,2,3} to left side numbers or start numbers of sequence.
Why do we add 4 number sequence?
Because 4 is the maximum value of Cnt.
So, we form the range Rng using this construction,
If values in the 1st row of Rng is greater than 1st row of Right side number which is 2, then give zero length text string else Rng. So, in 1st row, 3 and 4 becomes blank.
in 2nd row 7 becomes blank and so on.
Now, SMALL function will help us to ignore zero length text strings and to convert multiple columns into one column or one row result.
Let me share the classic version epic formula solution,
Formatter takes large space and it will be hard to edit and view and so i didn't format.
Instead of enter, Use Ctrl + Shift + Enter in versions 2016 or earlier.
=SMALL(IF(LEFT(SUBSTITUTE($A$2:$A$7,"-",REPT(" ",5)),5)+TRANSPOSE(ROW(INDIRECT("1:"&MAX(RIGHT(SUBSTITUTE($A$2:$A$7,"-",REPT(" ",5)),5)-LEFT(SUBSTITUTE($A$2:$A$7,"-",REPT(" ",5)),5)+1)))-1)>(RIGHT(SUBSTITUTE($A$2:$A$7,"-",REPT(" ",5)),5)*1),"",LEFT(SUBSTITUTE($A$2:$A$7,"-",REPT(" ",5)),5)+TRANSPOSE(ROW(INDIRECT("1:"&MAX(RIGHT(SUBSTITUTE($A$2:$A$7,"-",REPT(" ",5)),5)-LEFT(SUBSTITUTE($A$2:$A$7,"-",REPT(" ",5)),5)+1)))-1)),ROWS($G$2:G2))
Improved version Without LAMBDA:
= LET(
A, $A$2:$A$7,
N, 5,
NT, REPT(" ", N),
L, LEFT(SUBSTITUTE(A, "-", NT), N),
R, RIGHT(SUBSTITUTE(A, "-", NT), N),
Cnt, R - L + 1,
Rng, L + SEQUENCE(, MAX(Cnt), 0),
SMALL(
IF(
Rng > (R * 1),
"",
Rng
),
SEQUENCE(
SUM(Cnt)
)
)
)
Logic is same as classic version, but we replaced Row/Indirect construction with SEQUENCE function and this will spill the range, so we don't have to drag it down.
LAMBDA version:
= DROP(
REDUCE(
0,
A2:A7,
LAMBDA(
s,
c,
VSTACK(
s,
LET(
N, 5,
NT, REPT(" ", N),
L, LEFT(SUBSTITUTE(c, "-", NT), N),
R, RIGHT(SUBSTITUTE(c, "-", NT), N),
Cnt, R - L + 1,
SEQUENCE(Cnt, , L)
)
)
)
),
1
)
Here, we don't have to form the range Rng. We can start the sequence from Left side numbers and for the number of rows we can use Cnt. VSTACK will help us to append series of numbers for each intervals.
Comments