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?
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.
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.