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 09, 2025

Oliver,

I sent you a reply a bit ago but I'm not seeing it in these threads so I am sending it again.  I was wondering if I can add a column on my ALL! sheet as I left out a Date Column.  I need to insert this column in the column F position on the ALL! sheet.  I noticed when I did this, all the data on the Vacant Rooms sheet disappeared and was replaced by #SPILL!.  I deleted the column I inserted and every thing came back.

How does the formula need to be adjusted to account for the new column on the ALL! sheet?

Thank you,

Carl

Resources