Create Code Sequence

[deciog]

Hi,

I would need a formula that would create column E according to the data that is in columns A, B and C.

Decio

 

[p45cal]

What version of Excel are you using?

 

[deciog]

Sorry, Excel 2016 e 365

thanks

Decio

 

[p45cal]

Excel 2016 e 365

What does this mean? Is it Excel 2016 or Office365?

 

[Peter Bartholomew]

What does this mean? Is it Excel 2016 or Office365?

I think it is 'and'; just a minor shift of language?

 

[deciog]

p45cal and Peter Bartholomew

The company has both Excel 2016 and Office 365 versions

Translation error as Peter commented

Decio

 

[Peter Bartholomew]

I can do the 365 bit, but converting it for 2016 is not going to be nice!

= LET(
    count,    1+end-start,
    close,    SCAN(0,count, LAMBDA(t,x,t+x)),
    open,     1 + close - count,
    k,        SEQUENCE(MAX(close)),
    row,      XMATCH(k,open,-1),
    instance, k+1-INDEX(open,row),
    baseCode, INDEX(Code,row),
    modifier, TEXT(INDEX(start,row)+instance-1,"0000"),
    VALUE(baseCode & modifier)
  )

 

[p45cal]

A straightforward Power Query solution in cell G1 which should work in both those versions of Excel.

 

[Peter Bartholomew]

A straightforward Power Query solution in cell G1 which should work in both those versions of Excel.

Nice! Mind you, 'each List.Transform(([start]..[End])' is not quite as straightforward for me as it obviously is for you.

 

[bosco_yip]

An old school formula for old Excel versions (Excel 2010 and up).

In F2, formula copied down :

=IFERROR(INDEX(A$2:A$11&TEXT(B$2:B$11,"0000"),AGGREGATE(15,6,ROW($1:$10)/((C$2:C$11-B$2:B$11+1)>=COLUMN(A:Y)),ROW(A1)))+RIGHT(AGGREGATE(15,6,ROW($1:$10)/1%+COLUMN(A:Y)/((C$2:C$11-B$2:B$11+1)>=COLUMN(A:Y)),ROW(A1)),2)-1,"")

 

[p45cal]

="" & FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(", ",TRUE,BYROW(A2:C11,LAMBDA(a,ARRAYTOTEXT(INDEX(a,1)&TEXT(SEQUENCE(INDEX(a,3)-INDEX(a,2)+1,,INDEX(a,2)),"0000"),0)))), ", ", "</s><s>" ) & "</s></t>","//s")

 

[deciog]

p45cal Good morning.

My version doesn't have this ARRAYTOTEXT function Using the site https://en.excel-translator.de/translator/ , translating from English to Portuguese (Brazil) doesn't have this translation either

I updated it but it didn't help, this formula didn't work.

Could you post the model with this formula so that I can see if it will automatically translate


Thanks

Decio



Peter Bartholomew Good Morning.

I need you to post in the model because translating didn't work

I thank

Decio

bosco_yip Good morning.

It worked perfectly thank you

Decio

 

[p45cal]

My version doesn't have this ARRAYTOTEXT function Using the site https://en.excel-translator.de/translator/ , translating from English to Portuguese (Brazil) doesn't have this translation either

I updated it but it didn't help, this formula didn't work.

Could you post the model with this formula so that I can see if it will automatically translate

You don't really need ARRAYTOTEXT - I was just exploring some of these newish functions.
In the attached, the Power Query solution, a formula with ARRAYTOTEXT, and a formula without ARRAYTOTEXT

 

[deciog]

p45cal


With the template attached, the formula was translated and I loved it because it has a lot of features.

In English ARRAYTOTEXT translated to Portuguese Brazil MATRIZPARATEXTO

Worked perfectly

Thanks

 

[bosco_yip]

Here is another shorter formula option, being worked for Excel 2016 and up

In G2, formula copied down :

=IFERROR(1/(1/ROUND(IFNA(LOOKUP(1,0/(ROW(A1)=MMULT(N(COLUMN(A:J)<ROW($1:$11)),C$2:C$11-B$2:B$11)+ROW($1:$11)),A$2:A$12&TEXT(B$2:B$12,"0000")),G1+1),0)),"")

 

[deciog]

bosco_yip, Bom Dia.

This formula is also excellent works perfectly and faster

Thank you very much

Decio

 

[Excel Wizard]

For MS365 Please try

=LET(z,A2:C11,a,INDEX(z,,1),b,INDEX(z,,2),r,ROW(z),n,1+INDEX(z,,3)-b,m,MMULT(--(r>TRANSPOSE(r)),n),s,SEQUENCE(SUM(n),,0),LOOKUP(s,m,a*10^4+b)+s-LOOKUP(s,m))

or with new TOCOL function

=LET(z,A2:C11,a,INDEX(z,,1),b,INDEX(z,,2),n,1+INDEX(z,,3)-b,m,SEQUENCE(,MAX(n)),TOCOL(IF(n>=m,a*10^4+m+b-1,NA()),3))

 

[bosco_yip]

In the practical work, in order to avoid complicated formula, to adopt helper column and simpler formula is a nice option.

Here is a simpler formula with helper option, being worked for Excel 2016 and up

1] In D2 (helper), formula copied down :

=SUMPRODUCT(N(+C$1:C1)-N(+B$1:B1)+1)

2] In G2 (Result), formula copied down :

=IFERROR(1/(1/IFNA(INDEX(A$1:A$11&TEXT(B$1:B$11,"0000"),MATCH(ROW(A1),D:D,0)),G1+1)),"")

 

[Peter Bartholomew]

I need you to post in the model because translating didn't work

Reimplemented from my earlier post!
The underlying strategy is somewhat similar to @bosco_yip 's helper column but using the SCAN function.