concat with sequence number

[jack29]

I have quite few 100's rows in my excel, which needs to be added with below formula and dragged across down. I have tried SEQUENCE formula with no luck.


=CONCAT("<execute order="&TEXT(CHAR(34)&SEQUENCE(1,1,1,1)&CHAR(34),"Text"),"MR")

This will return below

<rule order="1"MR

Now, for starting cell it will be 1 number and when i drag to next cells i want that number to be incremented by 1 value, so it displays in next row as

<rule order="2"MR
Next row as

<rule order="3"MR

So on....

 

[Anthony47]

My guess:

=CONCATENATE("<execute order="&TEXT(CHAR(34)&SEQUENCE(100,1,1,1)&CHAR(34),"Text"),"MR")

This will return 100 lines, provides the cells below the one you populate with the formula is free (otherwise you will have the error #SPILL)

You might replace that "100" for example with the count of how many rows are in one of the columns; for example

=CONCATENATE("<execute order="&TEXT(CHAR(34)&SEQUENCE(COUNTA(C:C),1,1,1)&CHAR(34),"Text"),"MR")

This will return so many lines according how many cells are populated in column C

 

[jack29]

My guess:

=CONCATENATE("<execute order="&TEXT(CHAR(34)&SEQUENCE(100,1,1,1)&CHAR(34),"Text"),"MR")

This will return 100 lines, provides the cells below the one you populate with the formula is free (otherwise you will have the error #SPILL)

You might replace that "100" for example with the count of how many rows are in one of the columns; for example

=CONCATENATE("<execute order="&TEXT(CHAR(34)&SEQUENCE(COUNTA(C:C),1,1,1)&CHAR(34),"Text"),"MR")

This will return so many lines according how many cells are populated in column C

Sorry, I forgot to mention I have few cell references which gets auto incremented when i Drag Example:

=CONCAT("<execute order="&TEXT(CHAR(34)&SEQUENCE(1,1,1,1)&CHAR(34),"Text"),"MR", A2, B2)
=CONCAT("<execute order="&TEXT(CHAR(34)&SEQUENCE(1,1,1,1)&CHAR(34),"Text"),"MR", A3, B3)
=CONCAT("<execute order="&TEXT(CHAR(34)&SEQUENCE(1,1,1,1)&CHAR(34),"Text"),"MR", A4, B4)
So on

 

[Fluff]

How about

=LET(s,SEQUENCE(100),CONCATENATE("<execute order="&CHAR(34)&s&CHAR(34),"MR",INDEX(A2:A100,s),INDEX(B2:B100,s)))

 

[jack29]

How about

=LET(s,SEQUENCE(100),CONCATENATE("<execute order="&CHAR(34)&s&CHAR(34),"MR",INDEX(A2:A100,s),INDEX(B2:B100,s)))

Thanks for the solution, I tried above but strings are getting overlapped to next row. please view the attachment.

 

[Anthony47]

A possible option?

=CONCAT("<execute order="&TEXT(CHAR(34)&ROW(A1)&CHAR(34),"Text"),"MR", A2, B2)

Copy down as needed

 

[jack29]

A possible option?

=CONCAT("<execute order="&TEXT(CHAR(34)&ROW(A1)&CHAR(34),"Text"),"MR", A2, B2)

Copy down as needed

Wonderful, it's working thank you @Anthony47
Thank you @Fluff

 

[Fluff]

You need to remove the A2 & B2 from your formula.