create a formula for specific sequence

%3CLINGO-SUB%20id%3D%22lingo-sub-2826344%22%20slang%3D%22en-US%22%3Ecreate%20a%20formula%20for%20specific%20sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2826344%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20name%201.V1.S%2C%20name%201.V2.S%2C..........name%201.V4.S%2C%20name%202.V1.S.......etc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehow%20I%20could%20make%20a%20formula%20for%20the%20long%20list%3F%20Note%20the%20name%20is%20the%20same.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2826344%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2826562%22%20slang%3D%22en-US%22%3ERe%3A%20create%20a%20formula%20for%20specific%20sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2826562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179562%22%20target%3D%22_blank%22%3E%40Ashwaj%3C%2FA%3E%2C%20try%20this%20formula%20into%20cell%20A2%20and%20drag%20it%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%22name%20%22%26amp%3BINT((ROW()-ROW(%24A%241)-1)%2F4)%2B1%26amp%3B%22.V%22%26amp%3BMOD(ROW()-ROW(%24A%241)-1%2C4)%2B1%26amp%3B%22.S%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECell%20%24A%241%20is%20the%20header%20cell.%3C%2FP%3E%3CP%3EChange%20the%20header%20cell%20if%20you%20move%20the%20list%20to%20another%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEvery%204%20times%20the%20first%20number%20changes%2C%20the%20second%20number%20being%20a%20sequence%20from%201%20to%204%2C%20following%20your%26nbsp%3Bspecific%20sequence%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PedroWave_0-1633691094501.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316176i51EA8E45DF24C010%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PedroWave_0-1633691094501.png%22%20alt%3D%22PedroWave_0-1633691094501.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EPedro%20Wave%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2826909%22%20slang%3D%22en-US%22%3ERe%3A%20create%20a%20formula%20for%20specific%20sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2826909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179427%22%20target%3D%22_blank%22%3E%40PedroWave%3C%2FA%3E%26nbsp%3B%20Thanks%20a%20lot%2C%20fantastic%20exactly%20what%20I%20want%2C%20how%20clever.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2826989%22%20slang%3D%22en-US%22%3ERe%3A%20create%20a%20formula%20for%20specific%20sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2826989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179562%22%20target%3D%22_blank%22%3E%40Ashwaj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShould%20you%20be%20so%20fortunate%20as%20to%20be%20using%20Excel%20365%2C%20then%20the%20same%20formula%20could%20be%20written%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20k%2C%20SEQUENCE(100%2C1%2C0)%2C%0A%20%20%20%20r%2C%201%2BQUOTIENT(k%2C%204)%2C%0A%20%20%20%20c%2C%201%2BMOD(k%2C%204)%2C%0A%20%20%20%20CONCATENATE(%22name%20%22%2C%20r%2C%20%22.V%22%2C%20c%2C%20%22.S%22)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New 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

@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.