instead of filter, change the if statement to produce NA() then use the TOCOL() function to filter out the non-visible rows.. =TOCOL(UNIQUE(BYROW(Data[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,NA())))),3) Tocol makes a easy filter to get rid of blanks and errors, so by putting the NA() function in the false of the if, is easier to do then the LET and Filter()
Nice, I like it ✅ - this might be the 6th method I’ve seen for this formula now. Interresting use of TOCOL. Maybe I need to do some performance testing.
Fascinating. The only problem I find with LAMBDA and its help functions is how they make the formula almost unrecognisable from the original formula. My workaround, insert a couple of extra columns and break the formula down into parts to see how it changes. I also added some =FORMULATEXT cells to see at a glance how the formula changes. It works for me. Obviously, I downloaded your "...Complete" file and left it untouched to compare my WIP in the "...Start" file with the finished product. I'll need some practice.
Pre-LAMBDA adding columns to the data was pretty much the only way. That used to be my preferred method. However, for me it broke the basic principles of separating data, calculation and presentation. It adds a calculation element (the slicer selection) back into the data (the table). So I prefer the LAMBDA option, because I think it’s the right way to structure a spreadsheet.
@@ExcelOffTheGrid It's just a temporary fix until I fully get my head around LAMBDA. Once I've practiced a few times, it will no longer be necessary. As I'm retired, it's not as though I'll need it for work or anything. I'm just doing these tutorials to keep my brain active in an attempt to keep the dreaded dementia at bay.
Without using a LAMBDA =LET(uniqueList, UNIQUE(FILTER(PartsUsed[Br Branch Num], SUBTOTAL(3, OFFSET(PartsUsed[Br Branch Num], ROW(PartsUsed[Br Branch Num])-MIN(ROW(PartsUsed[Br Branch Num])), 0, 1)))), uniqueList) But honestly I like yours.
What I've used before is a helper pivot table (also connected to the slicer). This then shows just the selected items. Much, much simpler, so what am I missing??
There are always at least 3 ways of achieving equivalent results. Go for the solution you like the most. It is also always fun to learn new techniques to apply when you get stuck using the one you are used to performing.
I’ve used both methods too. The key differences are: 1) The Pivot Cache which the PivotTable is generated from does not exist in the calculation chain, so we need to rely on a refresh. This table method is more dynamic as it only requires the calculation chain. 2) A PivotTable is not an object we can reference directly. We have to reference a range which bigger than the PivotTable and hope it is big enough for the future. This method uses Tables which can be directly referenced and have auto expansion built-in. 3) By default PivotTables maintain a cache of previous field items, even if no longer I the data set, so may create the need to clear the cached items from time-to-time. So, the PivotTable approach increases risk of error and the need for manual maintenance. Once the fxSlicerSelection function set-up, I think it will be easier than building helper PivotTables.
@@maciejkopczynski55 Agreed. I did like and learn from the video. I just wanted to know if the technique in the video accomplishes something more than using a helper pivot table.
I don't know what to say, wonderful video, 💞💞 I suggest that it would be better if you zoom little bit while working in a specific part of a screen like writing formulas , it helps to focus more , thank you 😊
Can you explain how the disconnected table works? Are you using unique to get the items of a column, then hardcoding them into a table, then generating a slicer from this hardcoded table?
@@ExcelOffTheGrid Thank you. Do you have videos on the topic? Still hazy on what the workflow looks like and other videos on disconnected tables don't seem to be similar to the workflow described (as other discussions revolve around Power BI and Dax rather than use in excel).
once again - there is a much easier way to achieve your objective than all of these complications. Simplicity is key to success. Also a small advice - before diving into function and formula - make the objective of the tutorial clear - for me it wasn't clear as to what was the objective of making this video. "Let", "Byrow","Lambda" - before using these like a recorded script - it is essential to explain what am I trying to achieve in the first place
A helper PivotTable uses an object which is outside of the calculation chain. So it introduces 2 additional risk areas: 1) Having to reference an area bigger than the PivotTable in the hope the data doesn’t exceed the range. If it does it gives the wrong values. 2) Having to refresh the PivotTable.when data changes (which is de-risked if using Power Query) adds a manual step into the process. If a user forgets, the values are wrong. The method in this video avoids both of those issues. In the video we create a reusable formula which can be copy/pasted to another workbook. I believe that applying that formula is now significantly easier than having to create a helper PivotTable each time. Just my opinion.
instead of filter, change the if statement to produce NA() then use the TOCOL() function to filter out the non-visible rows..
=TOCOL(UNIQUE(BYROW(Data[Date],LAMBDA(r,IF(SUBTOTAL(3,r),r,NA())))),3)
Tocol makes a easy filter to get rid of blanks and errors, so by putting the NA() function in the false of the if, is easier to do then the LET and Filter()
Nice, I like it ✅ - this might be the 6th method I’ve seen for this formula now.
Interresting use of TOCOL.
Maybe I need to do some performance testing.
Very nice indeed.
Great Video
Glad you enjoyed it
Stellar video! Thank you for sharing!
Thanks - glad you enjoyed it. 😁
Fascinating. The only problem I find with LAMBDA and its help functions is how they make the formula almost unrecognisable from the original formula. My workaround, insert a couple of extra columns and break the formula down into parts to see how it changes. I also added some =FORMULATEXT cells to see at a glance how the formula changes. It works for me. Obviously, I downloaded your "...Complete" file and left it untouched to compare my WIP in the "...Start" file with the finished product. I'll need some practice.
Pre-LAMBDA adding columns to the data was pretty much the only way. That used to be my preferred method.
However, for me it broke the basic principles of separating data, calculation and presentation. It adds a calculation element (the slicer selection) back into the data (the table).
So I prefer the LAMBDA option, because I think it’s the right way to structure a spreadsheet.
@@ExcelOffTheGrid
It's just a temporary fix until I fully get my head around LAMBDA. Once I've practiced a few times, it will no longer be necessary. As I'm retired, it's not as though I'll need it for work or anything. I'm just doing these tutorials to keep my brain active in an attempt to keep the dreaded dementia at bay.
Absolutely efficient. Great field work. Thank you very much for sharing Mark.
Thanks Ivan. I hope you can put it to good use.
Dark sorcery this is =) Thank you!
You're very welcome!
Hi Mark: A great video and terrific solution. This topic has been top of mind for me lately. Thank you! You're a great presenter -- clear and concise.
Thanks Jim - I hope you can put it to good use.
Without using a LAMBDA
=LET(uniqueList, UNIQUE(FILTER(PartsUsed[Br Branch Num], SUBTOTAL(3, OFFSET(PartsUsed[Br Branch Num], ROW(PartsUsed[Br Branch Num])-MIN(ROW(PartsUsed[Br Branch Num])), 0, 1)))), uniqueList)
But honestly I like yours.
Excellent post as usual, thank you.
Thank You. I'm glad it was useful.
Awesome! 👍 I learned force row calculations using BYROW (), use LET (), and use IF inside LAMBDA.
Thanks! Been subscribed & followed
Awesome! Thank you! 👍
Thankyou, this is very helpful
What I've used before is a helper pivot table (also connected to the slicer). This then shows just the selected items. Much, much simpler, so what am I missing??
There are always at least 3 ways of achieving equivalent results. Go for the solution you like the most. It is also always fun to learn new techniques to apply when you get stuck using the one you are used to performing.
I’ve used both methods too. The key differences are:
1) The Pivot Cache which the PivotTable is generated from does not exist in the calculation chain, so we need to rely on a refresh. This table method is more dynamic as it only requires the calculation chain.
2) A PivotTable is not an object we can reference directly. We have to reference a range which bigger than the PivotTable and hope it is big enough for the future. This method uses Tables which can be directly referenced and have auto expansion built-in.
3) By default PivotTables maintain a cache of previous field items, even if no longer I the data set, so may create the need to clear the cached items from time-to-time.
So, the PivotTable approach increases risk of error and the need for manual maintenance.
Once the fxSlicerSelection function set-up, I think it will be easier than building helper PivotTables.
@@maciejkopczynski55 Agreed. I did like and learn from the video. I just wanted to know if the technique in the video accomplishes something more than using a helper pivot table.
@@ExcelOffTheGrid Very clearly explained. Thanks very much.
Wow, amazing technique... 365 is now sorcery compared to older versions. It seems so much easier to ask the computer to do anything these days.
That's a good term... "365 is now sorcery"... it certainly feels like that. It gives us so many better ways to solve problems.
I don't know what to say, wonderful video, 💞💞
I suggest that it would be better if you zoom little bit while working in a specific part of a screen like writing formulas , it helps to focus more , thank you 😊
Thanks for that feedback about the level of zoom. I appreciate it you letting me know.
Can you explain how the disconnected table works? Are you using unique to get the items of a column, then hardcoding them into a table, then generating a slicer from this hardcoded table?
They are loaded into a Table as part of a Power Query refresh.
So it updates whenever the data is updated.
@@ExcelOffTheGrid Thank you. Do you have videos on the topic? Still hazy on what the workflow looks like and other videos on disconnected tables don't seem to be similar to the workflow described (as other discussions revolve around Power BI and Dax rather than use in excel).
Very useful, thank you
Glad it was helpful!
Super👍❤
Thanks Kebin. 😁
👍❤
😁
once again - there is a much easier way to achieve your objective than all of these complications. Simplicity is key to success.
Also a small advice - before diving into function and formula - make the objective of the tutorial clear - for me it wasn't clear as to what was the objective of making this video. "Let", "Byrow","Lambda" - before using these like a recorded script - it is essential to explain what am I trying to achieve in the first place
What is the easier way that you mentioned?
@@ExcelOffTheGrid a pivot table(helper) or simple filter formula. I did this and got it very easily without complicating the objective
A helper PivotTable uses an object which is outside of the calculation chain. So it introduces 2 additional risk areas:
1) Having to reference an area bigger than the PivotTable in the hope the data doesn’t exceed the range. If it does it gives the wrong values.
2) Having to refresh the PivotTable.when data changes (which is de-risked if using Power Query) adds a manual step into the process. If a user forgets, the values are wrong.
The method in this video avoids both of those issues.
In the video we create a reusable formula which can be copy/pasted to another workbook. I believe that applying that formula is now significantly easier than having to create a helper PivotTable each time. Just my opinion.
@@ExcelOffTheGrid The amount of time and complexity involved in this is not the worth of yield in this case..just my opinion
Cool. Thanks for watching the video.
Hi,
Here's another way to get the selected items
=LET(rng,A2:A12,FILTER(rng,BYROW(rng,LAMBDA(a,SUBTOTAL(103,a)))))
That's nice.
I created about 4 different calculation for this... and I've created a better version in minutes. Good Work 👍
Thank you@@ExcelOffTheGrid