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),
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.
I rearranged the Occ Status list as you stated and it works just great. I have a question, I added a column on my ALL! sheet as I didn't realize I was missing a date column. When I did I found nothing on the Vacant Rooms! sheet except for #SPILL! was in cell A2 and all the data was missing. I deleted the column I added and every thing came back. What adjustments to the formula are needed for me to add my missing Date Column. I am wanting the new column in column F with a header of DOR.
Please help me out with this.
Many Thanks,
Carl
If i do this in the sample file the formula dynamically updates the results in the Vacant Rooms sheet. In the ALL sheet i've added column F with some sample data.
I intentionally created a #SPILL! error in cell A2 as you can see in the screenshot below (#ÜBERLAUF! in german Excel).
The reason for the #SPILL! error is that there is another formula in cell A3 as you can see in the screenshot below.
The spilled results of these formulas overlap and therefore return a #SPILL! error. You can delete any of these formulas to remove the error.
In the attached file i've added the DOR column F in the ALL! sheet.