Oct 08 2021 03:04 AM
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.
Oct 08 2021 04:11 AM
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:
Regards,
Pedro Wave
Oct 08 2021 05:37 AM
@PedroWave Thanks a lot, fantastic exactly what I want, how clever.
Oct 08 2021 06:18 AM
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")
)
Oct 08 2021 06:28 AM
Oct 08 2021 04:11 AM
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:
Regards,
Pedro Wave