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 ...
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(
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
)
)