Forum Discussion
Formual Error
Trying to create and excel sheet that shows the top 3 tour members for each month of the year but the formula i'm using to show me that information is showing up as an error and i'm confused as to wh...
Aaron04 ,
(see the screenshot above)
Apply in cell E1 below formula:
=LET(
mth, A2:A21, att, B2:B21, pt, C2:C21,
t, LET(wr, WRAPROWS(TEXTSPLIT(ARRAYTOTEXT(BYROW(UNIQUE(mth),
LAMBDA(a, LET(sr, SORT(FILTER(HSTACK(mth, att, pt),
IFERROR(SEQUENCE(ROWS(mth)) / (mth = a), FALSE)), 2, -1),
ARRAYTOTEXT(FILTER(sr, BYROW(TAKE(sr,, -1) = LARGE(TAKE(sr,, -1), {1,2,3}), OR))))))),, ", "), 3),
IF(ISNUMBER(--wr), --wr, wr)), chc, CHOOSECOLS(t, 1),
VSTACK(HSTACK("Name", TOROW(TEXT(UNIQUE(MONTH(chc & 1)) * 28, "mmm"))),
DROP(PIVOTBY(CHOOSECOLS(t, 2), MONTH(chc & 1), CHOOSECOLS(t, 3), SINGLE,, 0,, 0), 1))
)or apply in cell K1 below formula:
=LET(
mth, A2:A21, umth, MONTH(mth & 1), unq, UNIQUE(mth),
VSTACK(HSTACK("Month", "3 best Attendance"),
HSTACK(unq, BYROW(BYROW(SEQUENCE(ROWS(unq)),
LAMBDA(a, ARRAYTOTEXT(CHOOSECOLS(SORT(VSTACK(TOROW(CHOOSEROWS(TEXTSPLIT(TEXTJOIN(";",,
DROP(GROUPBY(umth, B2:B21, ARRAYTOTEXT,, 0),, 1)), ", ", ";"), a), 3),
TOROW(CHOOSEROWS(--TEXTSPLIT(TEXTJOIN(";",,
DROP(GROUPBY(umth, C2:C21, ARRAYTOTEXT,, 0),, 1)), ", ", ";"), a), 3)), 2, -1, TRUE), {1,2,3})))),
LAMBDA(b, TEXTBEFORE(b, ",", 3)))))
)Let me know if these formulas worked for you or not. If they work then please mark my reply as a Valid Answer and kindly upvote it.
IlirU