Excel: Array formula - How to rank alphanumeric data bottom up


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.


This question generated 17 answers. To proceed to the answers, click here.

This thread is current as of October 07, 2015.


For more resources for Microsoft Excel