Forum Discussion

MarleyDawg's avatar
MarleyDawg
Copper Contributor
May 30, 2023
Solved

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!!!!

 

  • MarleyDawg 

    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.

Resources