Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Apr 07, 2025
Solved

Formula Help

Hello Everyone, I have attached a sample of my workbook and here is what I am trying to accomplish.  I have a Tab called ALL! (yes I know, will change it some day) that houses names and information ...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 08, 2025

    In sheet Lists in the attached file you can enter your priorities as shown in range C4:C20. The formula in the attached file displays the data ordered according to the values of range C4:C20.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 08, 2025
=LET(cnt_occ_status,COUNTIFS(ALL!T3:T170,Lists!A4:A20),
filtered_occ_status,FILTER(HSTACK(Lists!A4:A20,cnt_occ_status),cnt_occ_status),
IFNA(REDUCE(HSTACK(ALL!A2:T2,"Group count"),INDEX(filtered_occ_status,,1),LAMBDA(u,v,VSTACK(u,HSTACK(FILTER(ALL!A3:T170,ALL!T3:T170=v,""),XLOOKUP(v,INDEX(filtered_occ_status,,1),INDEX(filtered_occ_status,,2))),"",""))),""))

Is this similar to what you are looking for?

 

Carl_61's avatar
Carl_61
Iron Contributor
Apr 08, 2025

I also just noticed, when I select some of my Occ Statuses which are Data Validation picks such as OP, OP DTS, OP BAH, OP ETP, OP PCS, OP CNA & TU, all the data on the Vacant Rooms sheet disappears.  Only some of the selections such as D, V, IB, IP, O, E4O3, RTI, RTO produce data on the Vacant Rooms Sheet.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 08, 2025

    I can't rebuild this situation because there aren't dropdowns in my sample file. However if i select only some of the Occ Statuses as shown in the above screenshot then the formula returns the result below. Does this show your desired output?

Resources