Array formula - How to rank alphanumeric data bottom up

[gboyer]

Hi guys,
I am new on this forum and have never used a forum before.
I have an issue with an array formula.
I have made a seat allocation table on excel, showing which seats are available, and which ones are not. It is updating automatically.
When there is a new joiner in the company, I want my formula to find the available seat. Each available seat has an alphanumeric value starting with #B0, ranging from #B01 to #B09.
Here is the array formula I am currently using to display automatically the first available seat.

=IF("NAME OF NEW JOINER="","",IFERROR(INDEX(tbl,MIN((IF((COUNTIF($W$5:W5,tbl)=0)*(LEFT(tbl,LEN("#B"))="#B"),ROW(tbl)-MIN(ROW(tbl))+1))),MATCH(0,COUNTIF($W$5:W5,INDEX(tbl,MIN(IF((COUNTIF($W$5:W5,tbl)=0)*(LEFT(tbl,LEN("#B"))="#B"),ROW(tbl)-MIN(ROW(tbl))+1)),,1))+(LEFT(INDEX(tbl,MIN(IF((COUNTIF($W$5:W5,tbl)=0)*(LEFT(tbl,LEN("#B"))="#B"),ROW(tbl)-MIN(ROW(tbl))+1)),,1),LEN("#B"))<>"#B"),0),1),""))

W5 is the word "Seat"
tbl is the table representing the office, a desk is marked with #B

In this table, from top to bottom, there is:
#B05 #B06 #B07 #B08
#B04 #B03 #B02 #B01
#B09

In this table, the first available seat is #B05 (as the formula will look from top to bottom)
If I see the following table

Adam #B06 #B07 #B08
#B04 Lorna #B02 #B01
#B09

then the first available seat is #B06.

I need to find within this table using my array formula the first available seat BUT FROM THE LOWEST ALPHANUMERIC VALUE TO THE HIGHEST.
In this case, I want the answer to be #B01. If #B01 is used by someone, then the next available result should be #B02, then #B04, until #B09.

Could you please help me to achieve that.
I tried to add a "<"& in the COUNTIF functions of this formula but nothing has changed.
See by yourself

=IF("NAME OF NEW JOINER="","",IFERROR(INDEX(tbl,MIN((IF((COUNTIF($W$5:W5,"<"&tbl)=0)*(LEFT(tbl,LEN("#B"))="#B"),ROW(tbl)-MIN(ROW(tbl))+1))),MATCH(0,COUNTIF($W$5:W5,"<"&INDEX(tbl,MIN(IF((COUNTIF($W$5:W5,"<"&tbl)=0)*(LEFT(tbl,LEN("#B"))="#B"),ROW(tbl)-MIN(ROW(tbl))+1)),,1))+(LEFT(INDEX(tbl,MIN(IF((COUNTIF($W$5:W5,"<"&tbl)=0)*(LEFT(tbl,LEN("#B"))="#B"),ROW(tbl)-MIN(ROW(tbl))+1)),,1),LEN("#B"))<>"#B"),0),1),""))

I am out of options now.

Thank you.

 

[XOR LX]

1/ It shows 0 as a result.

Perhaps there are some blank cells in the range which you didn't tell me about?

2/ The problem is not solved as the results should display #B01, then #B02, etc...to show all the available ones in the alphanumeric order from low to high.

No problem:

=INDIRECT(TEXT(MIN(IF($A$1:$D$3<>"",IF(COUNTIF($A$1:$D$3,"<"&$A$1:$D$3)=SMALL(IF($A$1:$D$3<>"",COUNTIF($A$1:$D$3,"<"&$A$1:$D$3)),ROWS($1:1)),10^5*ROW($A$1:$D$3)+COLUMN($A$1:$D$3)))),"R0C00000"),0)

Copy down as required. I've added in a clause to account for blanks in the range as well.

Regards

 

[gboyer]

Thank you very much XOR LX, it helps a lot. I now have #B01, #B02, #B04, #B06 up to #B09 but after #B09, I should have nothing more. Instead, I have other cell results that have nothing to do with the #B criteria, but are part of another table that is underneath the first table. Only results that start with #B are in the first table.
Then after these non related results, I have #NUM all the way until the last row.

I actually need blank cells after #B09.

Thank you again.

 

[XOR LX]

but after #B09, I should have nothing more. Instead, I have other cell results that have nothing to do with the #B criteria, but are part of another table that is underneath the first table.

But that's not possible if the range you used was for your first table only.

Then after these non related results, I have #NUM all the way until the last row. I actually need blank cells after #B09.

Fair enough. If the number of expected returns is not too large (i.e. more than a few hundred or so), we can simply add an IFERROR clause:

=IFERROR(INDIRECT(TEXT(MIN(IF($A$1:$D$3<>"",IF(COUNTIF($A$1:$D$3,"<"&$A$1:$D$3)=SMALL(IF($A$1:$D$3<>"",COUNTIF($A$1:$D$3,"<"&$A$1:$D$3)),ROWS($1:1)),10^5*ROW($A$1:$D$3)+COLUMN($A$1:$D$3)))),"R0C00000"),0),"")

Regards

 

[gboyer]

Thank you for the IFERROR function. Still if all seats are busy, the next available seat should be a "blank cell". If you try to put a name instead of #B0 in the selected range, this name will appear at the end of the array formula. So if you replace #B1 by Adam, and all other seats are available (#B), normally, I should see results ranging from #B2 to #B9, and nothing after #B9, not even Adam.

Hope you get my point. Is it not possible to look ONLY for #B type of cells?

Thank you very much for your patience.

 

[XOR LX]

Ah! Yes - sorry. I forgot that there were other, non-desirable, non-blank entries in there.

=IFERROR(INDIRECT(TEXT(MIN(IF(LEFT($A$1:$D$3,2)="#B",IF(COUNTIF($A$1:$D$3,"<"&$A$1:$D$3)=SMALL(IF(LEFT($A$1:$D$3,2)="#B",COUNTIF($A$1:$D$3,"<"&$A$1:$D$3)),ROWS($1:1)),10^5*ROW($A$1:$D$3)+COLUMN($A$1:$D$3)))),"R0C00000"),0),"")

Regards

 

[gboyer]

Thank you, thank you, thank you, that is impressive what you did.
It works perfectly!!

 

[XOR LX]

You're welcome!

Cheers