Array formula - How to rank alphanumeric data bottom up

G

gboyer

New Member
Joined
Oct 7, 2015
Messages
9
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi.

I'm afraid I can't offer a solution with reference to the structured table that you are using, as it's not easy to work out what's what.

However, perhaps you can adapt this to meet your needs, based on data in A1:D3, array formula**:

=INDIRECT(TEXT(MIN(IF(COUNTIF(A1:D3,"<"&A1:D3)=MIN(COUNTIF(A1:D3,"<"&A1:D3)),10^5*ROW(A1:D3)+COLUMN(A1:D3))),"R0C00000"),0)

Note that there is no need to explicitly test for the initial character within the strings in this range, since Excel will always consider the string "#" to be "less than" "A".

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
 
Upvote 0
Hi XOR LX,

Thanks for the quick reply. What would you need to help me sort this out. The table range, the excel sheet itself? The excel sheet is heavy and complex, and as it is very sensitive information, I cannot send it on this forum. Is it possible to share it MP?
 
Upvote 0
Can you not simply adapt the ranges in my formula to match the Structured References in your table? Should be quite straightforward, no?

Regards
 
Upvote 0
I am sorry but I am not an expert in Excel. This formula you see, I found it on internet and worked it out a little to match my criteria.
Having your formula on top of this, I have no clue how to mix them up.

Would you mind helping me with this please. I can send you my sheet if you want.

Thank you.
 
Upvote 0
But why do you need to "mix them up"? What is the range in which your data is contained? I presumed for the sake of argument that it was in A1:D3. If it's not, then simply replace all instances of A1:D3 in the formula I gave with whatever the actual range is.

If you're using a Table, so that references to cells in that table are not of the "standard" form, e.g. A1, C3, etc., but instead of a "structured" form, e.g. [Column1], Table1[[#All],[Column1], etc., then that's something we can potentially look at changing. However, there is nothing to say that you cannot mix the two referencing types, so my formula should still work, providing you replace A1:D3 appropriately.

Regards
 
Upvote 0
I tried your formula but it doesn't work. I changed the table for C4:K21 but kept the criteria "R0C00000", even though I don't see the relationship between #B01 and ROC00000.
I changed ROC00000 for #B00 but it doesn't work either.
 
Upvote 0
Why "didn't it work"? What results did it give? Did you follow the instructions I gave re entering an array formula correctly?

As long as you are using an English-language version of Excel then the "R0C00000" should remain just as it is (it is independent of the values and range being queried).

Regards
 
Upvote 0
1/ It shows 0 as a result. I did everything as per your recommendations.
Then I created a new sheet and imputed the data from A1 to D3. I plotted your formula on F1 and it gave me #B1, #B06 and #B09. I froze the cells in your formula in order to have only one result, the LOWEST.
The reasons the formula displays 0 as a result instead of #B01 is because #B01 is itself the result of a formula (not an array formula, just a simple IF formula).
When I imput manually the data from A1 to D3 it gives me #B01.

How can we fix that?

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.

Thank you very much for your support.
 
Last edited:
Upvote 0
I managed to fix the first issue with the 0.
Now I just need the results to display #B01, then #B02, etc...

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Update formula on mouse click without macro
Replies
3
Views
207
Fluff
Fluff
R
  • Solved
How to convert table to column data
Replies
6
Views
396
alansidman
A
S
  • Solved
Rank a list
Replies
8
Views
438
Fluff
Fluff
T
  • Question
Countif formula returns 0 instead of actual value
Replies
2
Views
219
tigerzen
T
E
  • Solved
Find "nth" input, whilst using a filter
Replies
2
Views
119
ellison
E

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,992
Latest member
prabhuk279

Share this page

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