"#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. .">
Most valuable professional
  • Hot Topics

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.

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

This thread is current as of October 7, 2015.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel