Please help me convert this to array formula

[mrblister]

Hello all. I have the following formula:

=mid($C4,16,2)&"."&text(((countif(E$3:E3,mid(mid($C4,16,2)&".",1,FIND("@",SUBSTITUTE(mid($C4,16,2)&".",".","@",LEN(mid($C4,16,2)&".")-LEN(SUBSTITUTE(mid($C4,16,2)&".",".",""))),1))&"*"))*10)+10,"0000")

That formula is pasted in cell E4, and copied down to E1003. I want to replace that with a single array formula in E4. I'm having issues doing it; either the counter won't increment at all or I get circular dependencies. Can anyone assist?

Thanks in advance!

 

[Peter_SSs]

Instead of helpers having to try to reverse engineer that formula to produce some test data, it would be much better (& get you quicker answers) if you gave us a small set (say 5 rows) with XL2BB to work with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[kvsrinivasamurthy]

Without data and expected result it is very difficult.
Try this spill out formula in E4

=LET(dt,$C4:$C1003,a,MID(dt,16,2),b,$E$3,za,(a&"."&TEXT(((COUNTIF(OFFSET(b,0,0,SEQUENCE(ROW(dt)-ROW(b),1,1)),MID(a&".",1,FIND("@",SUBSTITUTE(a&".",".","@",LEN(a&".")-LEN(SUBSTITUTE(a&".",".",""))),1))&"*"))*10)+10,"0000")),za)

 

[Peter_SSs]

Give this a try in E4. If it does not return what you want for all rows then refer back to post #2.

=BYROW(C4:C1003,LAMBDA(r,LET(m,MID(r,16,2),m&"."&TEXT(10*COUNTIF(C$4:r,REPT("?",15)&m&"*"),"0000"))))

Edit: Oops, I forgot you are on version 2021 so you will not have BYROW I believe.

 

[Peter_SSs]

I forgot you are on version 2021 so you will not have BYROW I believe.

So try this one instead

=LET(d,C4:C1003,m,MID(d,16,2),m&"."&TEXT(10*COUNTIF(OFFSET(C4,0,0,SEQUENCE(ROWS(d))),REPT("?",15)&m&"*"),"0000"))

 

[mrblister]

@kvsrinivasamurthy and @Peter_SSs, thank you both for your responses!

Instead of helpers having to try to reverse engineer that formula to produce some test data, it would be much better (& get you quicker answers) if you gave us a small set (say 5 rows) with XL2BB to work with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

OK, I will do that from now on. Thank you for the advice!

 

[Peter_SSs]

thank you both for your responses!

You're welcome. Glad that formula worked for you.

BTW, it can be shortened very marginally

=LET(m,MID(C4:C1003,16,2),m&TEXT(10*COUNTIF(OFFSET(C4,0,0,SEQUENCE(ROWS(m))),REPT("?",15)&m&"*"),"\.0000"))