Please help me convert this to array formula

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
215
Office Version
  1. 2021
Platform
  1. Windows
Hello all. I have the following formula:

Excel 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!
 
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.)
 
Upvote 0
Without data and expected result it is very difficult.
Try this spill out formula in E4
Excel Formula:
=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)
 
Upvote 0
Give this a try in E4. If it does not return what you want for all rows then refer back to post #2.

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

Edit: :oops: Oops, I forgot you are on version 2021 so you will not have BYROW I believe.
 
Last edited:
Upvote 0
I forgot you are on version 2021 so you will not have BYROW I believe.
So try this one instead

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

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top