Looking for a dynamic formula to copy down

I am looking for a formula that will allow me to sort this data by location and workgroup. It is easier for me to attach and example. I have highlighted in yellow what I need the formula result to be. The issue is this data is dynamic and has a different amount of rows each time. What is consistent is the locations (highlighted in black) are always above the workgroups.

You can see where I started it (below the yellow), but was easier to illustrate what I am looking for like this. In other words, everything in yellow I need a formula to get that data

Any help is appreciated!

Change D2 to:

=IFERROR(MID(LEFT(F3,FIND("(",F3)-1),FIND("-",F3)+2,LEN(F3)),"")

copied down. Then delete all expected results and use:

=C2&" "&SCAN("",D3:D38,LAMBDA(x,y,IF(y="",x,y)))

1. The formula should be entered ONLY in the first row. No copying down.

2. Delete and try again.

3. If you STILL get #NAME, your version of O365 may not support SCAN.

4. Check the versuin by looking at the account page.

5. Alternatively, use:
=C2&LET(a,D3:D500,B,FILTER(a,F3:F500>0),c,SEQUENCE(ROWS(B)),LOOKUP(c,c/(B>""),B))

ONLY in c3.

Does it work in the sample file I posted at post 4?


Is there a version 2008?

Post a screenshot of yourproduct andversion,like minein Post 4.

1. You did not answer my question.

2. Your attachment is invalid/corrupt.

3. I will try again.

4. When I open the file attached HERE, I see this.
WHAT DO YOU SEE?

5. Was it working in MY file?

6. Did it fail ONLY in your file?

7. There is NOTHING in my formula that should be incompatable with any version of O365... which is what you say you are using.

8. Please retake a screenshot of your account page and post it.

Sorry I will try and be more clear. When I first open the file yes I see exactly that. When I add data in F285 the formula goes away and I see only this. The data in column F will not always be the same

Does that make more sense? Having issue attaching a long pic, hang on

2023-01-17_21-40-38.jpg

I copy/pasted some data from further up the column into F285 and it worked perfectly

Post the file which has the #Name error.

You STILL have not posted a picture with your Product/version. Please do so now.

Thanks. I see the image. Your product is bit old... and does not receive updates. O365 and what it does/does not support is massively confusing.

The file I attached at Post 8 SHOULD have worked OK. I'm on my phone right now (no Excel on it) but will check on lsptop in about 45 mins.

In the meantime... please check to see if all 3 of these are available on your Excel version:

LET
FILTER
SEQUENCE

OK. Let's pretend you DON'T have O365...

Try this:

I set up a Named Range (called Rng.... CTRl-F3 to view/edit)

='(Current) Bin Summary - 3 level'!$D$3:INDEX('(Current) Bin Summary - 3 level'!$D:$D,MATCH("ZZzzz",'(Current) Bin Summary - 3 level'!$F:$F))

I then used this:

=C2&LOOKUP(ROW(Rng),ROW(Rng)/(Rng<>""),Rng)

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.