Forum Discussion
Formula Help
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.
=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?
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
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.
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?
This formula worked and its pretty much what I was trying to achieve. There are some issues though maybe you help me with. Is there a way to have the headers be a part of each group? Another words, whenever a group is separated, can the headers head each group?
Can the Headers be of Bold Text?
When cell on the ALL Sheet is blank, can it also be blank on the Vacant Rooms sheet? right now, all cells blank on the ALL Sheet are showing 0 in them. In my data, column J are dates of births and Vacant (V) and Down Rooms (D) don't have dates on the ALL Sheet and therefore the dates for Vacant and Down rooms are showing as 1/0/1900. Would rather just have these cells or any cells with dates, blanks or zeros, just be blank on the Vacant Rooms sheet.
When there is a separation of Groups, can the Occ Status be displayed above the Headers for each group as follows: Group: VACANT. Group: Down. Group: IB and so on? Such as Column A, Group: In column B, VACANT.
Then at the bottom of the Group, Column A, VACANT Total: then to the right of it, place the count #. IE: VACANT TOTAL: 5
DOWN TOTAL: 10
IB TOTAL:2
And so on.
Remove the Group Count from column U
Thank you for any other assistance you can provide.
Carl
=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), result,IFNA(DROP(REDUCE(ALL!A2:T2,INDEX(filtered_occ_status,,1),LAMBDA(u,v,VSTACK(u,"Occ Status "&v,ALL!A2:T2,HSTACK(FILTER(ALL!A3:T170,ALL!T3:T170=v,"")),HSTACK(v& " Total",XLOOKUP(v,INDEX(filtered_occ_status,,1),INDEX(filtered_occ_status,,2))),"",""))),1),""),IF(result=0,"",result))The above formula produces the result shown in the screenshot in my sample sheet. The headers can't be formatted in bold text by a formula.
Thank you, you pretty much nailed it. I do not know what caused that issue of data disappearance when certain codes were selected but I deleted the formula from the sheet and re-copied and pasted it and it worked as intended.
Many thanks for your support on this. I am just checking it out now to see if placement of things all considered are where they need to be. I don't know if there is a way to Prioritize Occ Statuses but if there is, I'd like to have Occ Status "V" starting at the top followed by Occ Status "D" and then Occ Status "TU" and after that Occ Status "IB" and after that Occ Status "IP" and after that Occ Status "RTI" and after that Occ Status "RTO" and after that Occ Status "OP", "OP BAH", "OP CNA", "OP DTS", "OP EAS", "OP ETP", "OP MAR", "OP PCS", "OP E4O3".
If there is a way to dictate the order of Occ Status it would be great.
