Forum Discussion
MarleyDawg
May 30, 2023Copper Contributor
Sequence Function based on 2 values
Hello Excel Community - Not sure if the sequence function can do this, but I figured I'd ask someone smarter than I.
I love the Sequence function! I am using it for Levels. I enter the number of levels, lets say 12 and on my back up page, I am able to create rows 1, 2, 3...through 12 (or whatever value I enter into a specific field).
Now my folks are asking for further sequencing....now they are looking for zones. If 3 is entered, the rows should be created;
1-1
1-2
1-3
2-1
2-2
2-3
3-1
...
12-3
I am not sure that the sequence function is the right thing to do, but also not sure what function will do what I am looking for.
Thank you in advance, Excel Gurus!!!!
- MarleyDawgCopper ContributorThis is perfect!!
MarleyDawg , glad to help
I don't know how to make SEQUENCE repeat values. Here is a formula that will work in older versions of Excel too.
Name the cell with the number of levels Levels, and name the cell with the number of zones Zones.
In a cell in row 1, enter the formula
=IF(ROW()>Levels*Zones,"",QUOTIENT(ROW()-1,Zones)+1&"-"&MOD(ROW()-1,Zones)+1)
Fill down a long way.