SOLVED

create a formula for specific sequence

Copper Contributor

Hello 

 

I have a list of name 1.V1.S, name 1.V2.S,..........name 1.V4.S, name 2.V1.S.......etc

 

how I could make a formula for the long list? Note the name is the same.

4 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Ashwaj, try this formula into cell A2 and drag it down:

="name "&INT((ROW()-ROW($A$1)-1)/4)+1&".V"&MOD(ROW()-ROW($A$1)-1,4)+1&".S"

Cell $A$1 is the header cell.

Change the header cell if you move the list to another range.

 

Every 4 times the first number changes, the second number being a sequence from 1 to 4, following your specific sequence:

 

PedroWave_0-1633691094501.png

Regards,

Pedro Wave

@PedroWave  Thanks a lot, fantastic exactly what I want, how clever. 

@Ashwaj 

Should you be so fortunate as to be using Excel 365, then the same formula could be written

= LET(
    k, SEQUENCE(100,1,0),
    r, 1+QUOTIENT(k, 4),
    c, 1+MOD(k, 4),
    CONCATENATE("name ", r, ".V", c, ".S")
  )
I am so fortunate Peter, thanks a lot, This is very kind support.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Ashwaj, try this formula into cell A2 and drag it down:

="name "&INT((ROW()-ROW($A$1)-1)/4)+1&".V"&MOD(ROW()-ROW($A$1)-1,4)+1&".S"

Cell $A$1 is the header cell.

Change the header cell if you move the list to another range.

 

Every 4 times the first number changes, the second number being a sequence from 1 to 4, following your specific sequence:

 

PedroWave_0-1633691094501.png

Regards,

Pedro Wave

View solution in original post