Draft Pick Table Generation (aka Concatenated Sequence Based On Multiple Variables For List From Range)

user_13961017 0
Viewed 42

I hope my title is sufficient - I really wasn't sure what to even call this problem.

In the sheet, you will see two variables - B1= # of Rounds & B2= # of Picks Per Rounds.

I'm hoping to create what's essentially a concatenated list based on those two variables. So if someone listed the # of Rounds as 1 and the number of picks as 12, the output would be:

1.01,1.02,1.03,1.04,1.05,1.06,1.07,1.08,1.09,1.10,1.11,1.12.

If you had 2 round and 4 picks per round, the output would be:

1.01,1.02,1.03,1.04,2.01,2.02,2.03,2.04.

This generated list will be used for a data validated dropdown.

What I've come up with sort of works in a very limited sense, in addition it's awfully messy.

Here's what I did: Step 1: Used Sequence on both B1 & B2 to generate lists. (in the sheet, they are in E4 and F4 respectively. Step 2:

=FILTER(
{ARRAYFORMULA(IF(F4:F15<>"",IF(LEN(F4:F15)=1,E4&".0"&F4:F15,IF(LEN(F4:F15)=2,E4&"."&F4:F15)),));
ARRAYFORMULA(IF(F4:F15<>"",IF(LEN(F4:F15)=1,E5&".0"&F4:F15,IF(LEN(F4:F15)=2,E5&"."&F4:F15)),))},

{ARRAYFORMULA(IF(F4:F15<>"",IF(LEN(F4:F15)=1,E4&".0"&F4:F15,IF(LEN(F4:F15)=2,E4&"."&F4:F15)),));
ARRAYFORMULA(IF(F4:F15<>"",IF(LEN(F4:F15)=1,E5&".0"&F4:F15,IF(LEN(F4:F15)=2,E5&"."&F4:F15)),))}<>"")

That formula really doesn't work - I just wanted to show people what I've done.

google-sheets-formula google-sheets if-statement concatenation arrays
2 Answers

An alternative

=ArrayFormula(flatten(sequence(B1)&"."&text(sequence(1,B2),"00")))

enter image description here

or (borrowing from @player0)

=ArrayFormula(flatten(sequence(B1)+sequence(1,B2)*0.01))
user_38949170

try:

=ARRAYFORMULA(QUERY(SORT(
 FLATTEN(SPLIT(REPT(QUERY(SEQUENCE(B1),,9^9)&" ", B2), " ",,)))+
 FLATTEN(SPLIT(REPT(QUERY(SEQUENCE(B2),,9^9)&" ", B1), " ",,))*0.01, 
 "where Col1 <> 0", ))

0

see: https://stackoverflow.com/a/63310569/5632629

user_56326290
Related