Very ingenious formulas, Jon! I would solve this challenge like this: • create a calculated column “vis” in the data table that indicates whether a row is visible or not. • use vis in the filter condition. • use any and all table slices I need right next to the PivotBy or GroupBy formula. I can do complex filter conditions that way.
The whole process was very brilliant. Especially this formula... =SORT(UNIQUE(INDIRECT("tblOrders["&O5&"]"))) Everything is very combinative. I love it. Thank you so much.
Thanks Richard! Great question! If you want to have multiple filter criteria you can multiply or add them together. You don't need HSTACK for this. Here is an example that would filter the Month No AND Source columns. (tblOrders[Mo No.]=1)*(tblOrders[Source]="Online") If you change the asterisk to plus then that would create OR logic and give you results if either criteria is true, instead of both criteria with AND logic. I hope that helps.
Great video!! Check these out, 2 tiny lambdas to replace slicers, checkboxes, etc. This is for 1 "slicer" only, for multiple slicers more complicated to publish here. SLICER(a,i,s,l) a: array i: clm index nr. s: selection arg: if omitted -> all ( all -> sort(unique(index(a,,i))) ) ; if not omitted ex: {1,3,5} l: list arg: interactive argument used to toggle between showing all selection when "l"=1 and "s" selection when "l" omitted =LAMBDA(a,i,[s],[l], LET( u,SORT(UNIQUE(INDEX(a,,i))), q,SEQUENCE(ROWS(u)), t,HSTACK("nr\cl ix",i), h,HSTACK(q,u), v,VSTACK(t,h), IF(l,v,IF(ISOMITTED(s),v,VSTACK(t,INDEX(h,TOCOL(s),{1,2})))))) ex: =SLICER(tblOrders,7) nr\cl ix 7 1 Q1 2 Q2 3 Q3 4 Q4 =SLICER(tblOrders,7,{2,3}) nr\cl ix 7 2 Q2 3 Q3 FLTSLC(a,slc) creates the filter pattern for a specific slicer; a: array ; slc: slicer address ( ex: reference B15# ) =LAMBDA(a,slc,ISNUMBER(XMATCH(INDEX(a,,INDEX(slc,1,2)),DROP(TAKE(slc,,-1),1)))) To get filtered results based on the "slicer" at B15 we call =PIVOTBY(tblOrders[Color],tblOrders[Year],tblOrders[Ordered],SUM,,1,,0,,FLTSLC(tblOrders,B15#))
😂 I felt the same way when I stumbled upon that trick. I originally used it for a totals row before we had HSTACK and VSTACK. Here's a link to that video if you want to geek out. 🙂 ruclips.net/video/pTbRwr4flQ8/видео.html
Very ingenious formulas, Jon!
I would solve this challenge like this:
• create a calculated column “vis” in the data table that indicates whether a row is visible or not.
• use vis in the filter condition.
• use any and all table slices I need right next to the PivotBy or GroupBy formula.
I can do complex filter conditions that way.
Jon, this is awesome. I went twice through your video and now I'm going to study the file and the formulas. Thanks Jon!
You're welcome! 😀
What a wonderful concept and brilliant code. Thank you for sharing! 👍👍👍
Thanks Kevin! 🙂
Really like how you maintained functionality, then added features to it!
Thanks! 🙌
The whole process was very brilliant. Especially this formula...
=SORT(UNIQUE(INDIRECT("tblOrders["&O5&"]")))
Everything is very combinative. I love it. Thank you so much.
Thanks Ivan! I appreciate the nice feedback and your support. 🙂
VERY NICE!
Thank you! Cheers! 😀
Excellent 👍
sir really wonderful think sir
Thanks! 😀
excellent video thx jon
Thanks Martin! 🙂
Hi Jon, where is the file link for free download? Thanks
Great video. BTW Can you HSTACK multiple filter criteria in the Filter argument of PIVOTBY and GROUPBY?
Thanks Richard! Great question! If you want to have multiple filter criteria you can multiply or add them together. You don't need HSTACK for this. Here is an example that would filter the Month No AND Source columns.
(tblOrders[Mo No.]=1)*(tblOrders[Source]="Online")
If you change the asterisk to plus then that would create OR logic and give you results if either criteria is true, instead of both criteria with AND logic.
I hope that helps.
@ExcelCampus Thanks. I should have thought of that!
The effect by clicking the spacebar, you can check or uncheck all of the checkboxes in the list. That's dynamite!!!
Yeah it's nice functionality and I think it will be useful in a lot of scenarios beyond this grid slicer.
Great video!! Check these out, 2 tiny lambdas to replace slicers, checkboxes, etc. This is for 1 "slicer" only, for multiple slicers more complicated to publish here.
SLICER(a,i,s,l)
a: array
i: clm index nr.
s: selection arg: if omitted -> all ( all -> sort(unique(index(a,,i))) ) ; if not omitted ex: {1,3,5}
l: list arg: interactive argument used to toggle between showing all selection when "l"=1 and "s" selection when "l" omitted
=LAMBDA(a,i,[s],[l],
LET(
u,SORT(UNIQUE(INDEX(a,,i))),
q,SEQUENCE(ROWS(u)),
t,HSTACK("nr\cl ix",i),
h,HSTACK(q,u),
v,VSTACK(t,h),
IF(l,v,IF(ISOMITTED(s),v,VSTACK(t,INDEX(h,TOCOL(s),{1,2}))))))
ex:
=SLICER(tblOrders,7)
nr\cl ix 7
1 Q1
2 Q2
3 Q3
4 Q4
=SLICER(tblOrders,7,{2,3})
nr\cl ix 7
2 Q2
3 Q3
FLTSLC(a,slc) creates the filter pattern for a specific slicer;
a: array ; slc: slicer address ( ex: reference B15# )
=LAMBDA(a,slc,ISNUMBER(XMATCH(INDEX(a,,INDEX(slc,1,2)),DROP(TAKE(slc,,-1),1))))
To get filtered results based on the "slicer" at B15 we call
=PIVOTBY(tblOrders[Color],tblOrders[Year],tblOrders[Ordered],SUM,,1,,0,,FLTSLC(tblOrders,B15#))
Very cool! Thanks for sharing!! 🙌
👍
I find it spectacular to see...
=FILTER(O6#,CHOOSECOLS(O6#:N6,1);O6#)
O6#:N6 !! Woww. Amazing.
I have to cool off my face.
😂 I felt the same way when I stumbled upon that trick. I originally used it for a totals row before we had HSTACK and VSTACK. Here's a link to that video if you want to geek out. 🙂
ruclips.net/video/pTbRwr4flQ8/видео.html