Forum Discussion
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 about individuals living in dorm rooms I manage. Based on the headers on the ALL sheet I'm sure the Headers will speak for themselves to the data I have on record for individuals residing in the rooms. The main thing here however is that I am trying to Group Data based on [Occ Status] shown in the [Occ Status] column on the ALL sheet. I am wanting to have REAL TIME grouping. So if a change is made to the [Occ Status], on the ALL sheet, it will affect the group, add to or take away from, the group showing on the Vacant Rooms sheet. The Occ Statuses can be seen on the Lists sheet. As part of this grouping, I am also trying to have 2 spaces between each group with a group count of records showing within the Occ Status. In Name Manager, I have a named range called StatusList.
On the Vacant Room sheet in cell A3 I have a formula that that returns #CALC! and I cannot figure out where the issue lies.
=LET(
headers, {"BLDG","WING","ROOM","NAME","GNDR","MAIN A/C","WINDOW A/C","PORTABLE","OCC STATUS"},
bldg, ALL!A3:A170,
wing, ALL!B3:B170,
room, ALL!C3:C170,
name, ALL!D3:D170,
gndr, ALL!H3:H170,
mainAC, ALL!N3:N170,
windowAC, ALL!O3:O170,
portable, ALL!P3:P170,
occStatus, ALL!T3:T170,
data, CHOOSE({1,2,3,4,5,6,7,8,9},
bldg, wing, room, name, gndr, mainAC, windowAC, portable, occStatus
),
blankRow, HSTACK("", "", "", "", "", "", "", "", ""),
blankBlock, VSTACK(blankRow, blankRow),
sectioned, MAP(
StatusList,
LAMBDA(s,
LET(
labelRow, HSTACK("Occ Status: " & s, "", "", "", "", "", "", "", ""),
filtered, FILTER(data, occStatus = s, SEQUENCE(0)),
IF(ROWS(filtered) > 0,
VSTACK(labelRow, filtered, blankBlock),
SEQUENCE(0)
)
)
)
),
VSTACK(headers, sectioned)
)
I used CHATGPT for the above formula but doing so is just not getting me the result I need.
Can anyone help me out here. I am using MS 365, Version 2501, Build: 18429.20200
Thank you for all offers of support.
Carl
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.
Thank you for your input regarding my formula. Its nice to know there are people out there are willing to help with mind boggling formulas.
Carl
=LET( rawdata, ALL!A2:T170, headers, { "BLDG", "WING", "ROOM", "NAME", "GNDR", "MAIN A/C", "WINDOW A/C", "PORTABLE", "OCC STATUS" }, data, CHOOSECOLS(ALL!A2:T170, MATCH(headers, TAKE(rawdata, 1), )), DROP( IFNA( REDUCE( headers, StatusList, LAMBDA(x, y, IF( OR(TAKE(data, , -1) = y), VSTACK( x, "Occ Status: " & y, FILTER(data, TAKE(data, , -1) = y), "", "" ), x ) ) ), "" ), -2 ) )=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.
How about this:
=LET( headers, {"BLDG","WING","ROOM","NAME","GNDR","MAIN A/C","WINDOW A/C","PORTABLE","OCC STATUS"}, bldg, ALL!A3:A170, wing, ALL!B3:B170, room, ALL!C3:C170, name, ALL!D3:D170, gndr, ALL!H3:H170, mainAC, ALL!N3:N170, windowAC, ALL!O3:O170, portable, ALL!P3:P170, occStatus, ALL!T3:T170, data, CHOOSE({1,2,3,4,5,6,7,8,9}, bldg, wing, room, name, gndr, mainAC, windowAC, portable, occStatus), blankRow, HSTACK("", "", "", "", "", "", "", "", ""), blankBlock, VSTACK(blankRow, blankRow), sectioned, REDUCE(VSTACK(headers), StatusList, LAMBDA(acc, s, LET( labelRow, HSTACK("Occ Status: " & s, "", "", "", "", "", "", "", ""), filtered, FILTER(data, occStatus = s, SEQUENCE(0)), IF(ROWS(filtered) > 0, VSTACK(acc, labelRow, filtered, blankBlock), acc ) ) ) ), sectioned )For some reason, this formula results in a #CALC! error.
