I need a single cell formula to create a sequence of numbers with a limit in Google Sheets as shown in the image.
3 rows repeat the value
then Increment by 5
Asked
Active
Viewed 476 times
1
-
picture not showing for me – Mohamed Elgazar Oct 15 '22 at 07:04
-
Kindly add input table and expected output table as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 15 '22 at 08:48
4 Answers
3
Use this formula, you can adjust the Sequence() and REPT(rg&",",3) parameters to your need.
In this example Sequence(number_of_unique_numbers,columns,start_at,increment_by)
And REPT(rg&",",Repeat_N_times)
=ArrayFormula(FLATTEN(SPLIT(BYROW(SEQUENCE(3,1,5,5),
LAMBDA(rg, IF(rg="",,REPT(rg&",",3)))),",")))
Option 02
Based on Themaster - answer we use lambda with the names.
u unique
s start
r repat n time
=LAMBDA(u,s,r, FLATTEN(MAKEARRAY(u,r,LAMBDA(u,r,u*s))))
(4,5,3)
Used formulas help
ARRAYFORMULA - FLATTEN - SPLIT - BYROW - SEQUENCE - LAMBDA - IF - REPT - MAKEARRAY
Osm
- 2,699
- 2
- 4
- 26
3
Use MAKEARRAY with FLATTEN. Multiply the row index number by 5:
=FLATTEN(MAKEARRAY(4,3,LAMBDA(i,j,i*5)))
| Output |
|---|
| 5 |
| 5 |
| 5 |
| 10 |
| 10 |
| 10 |
| 15 |
| 15 |
| 15 |
| 20 |
| 20 |
| 20 |
TheMaster
- 45,448
- 6
- 62
- 85
-
-
Because I did thought about `MAKEARRAY` and did't spend time to solve it. and your answer is the quickest shortcut to the perfect non named function solution. – Osm Oct 15 '22 at 10:59
-
@Osm As long as you give credit, go for it. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). – TheMaster Oct 15 '22 at 11:00
-
@Osm Yes, I was wondering what refactor could be shorter than this. Do you actually have a shorter method or a method with less number of operations? PS: I did upvote your answer for adding explanations. So, can't upvote anymore, if you do show something. – TheMaster Oct 15 '22 at 11:01
-
-
@Osm If you satisfactorily answer [this](https://stackoverflow.com/questions/74045896/how-to-get-permutations-from-factoradic-numbers-without-lambda-as-a-array-formul), bounty is yours :) – TheMaster Oct 15 '22 at 11:22
-
@TheMaster atOsm. You guys are awesome. All your solutions works fine even though i did not understand a thing. But there is a small catch. In my case, limit is the number of rows. if my limit is 7, then it should be like 5 5 5 5 5 10 10 . Can you guys please make that possible? I am sure you can. ;) – anwwwar Oct 17 '22 at 05:54
-
@anwwwar Kindly ask new question for any follow up question. Also research before asking. In this case, look into `ARRAY_CONSTRAIN`. If you're unable to figure it out, ask a new question. – TheMaster Oct 17 '22 at 05:56
0
try:
=INDEX(FLATTEN(SEQUENCE(4, 1, 5, 5)*SEQUENCE(1, 3, 1, 0)))
or:
=INDEX(FLATTEN(5*MAKEARRAY(4, 3, LAMBDA(x, O ,x))))
player0
- 124,011
- 12
- 67
- 124
0
A different approach, but probably not a good one...:
=arrayformula(mround(sequence(12,1,2),3)*(5/3))
The God of Biscuits
- 2,029
- 2
- 3
- 10
-
While this code snippet may be the solution, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – TheMaster Oct 16 '22 at 16:37