In last week’s video on YouTube I combined the new GROUPBY function with a Data Validation-generated drop-down. The idea behind it was to simulate a Pivot Table filter drop-down. But there was one important item missing from the drop-down…an ALL option – basically a way to clear the filter selection.
In this week’s video I demonstrate the solution that I came up with to fix this problem. You can watch the video via the links below.
This is the formula that I used to create the drop-down list:
=TRANSPOSE(TEXTSPLIT("All Locations, " & ARRAYTOTEXT(UNIQUE(SORT(Sales2[Customer Location]))),", "))
It looks complicated so let me break it down…
The ARRAYTOTEXT function takes a set of values and converts them into a single text string with each value separated by a comma and space. In this example, the set of values to be converted are the ones generated by the UNIQUE/SORT function, which itself creates an A-Z sorted list of the values in the Customer Location column and removes the duplicates.
The next step is to add (i.e. concatenate) the words “All Locations” (or just “All” if you prefer) to the beginning of the text string followed by comma-space. So we now have All Locations followed by comma space followed by each of the items generated by the UNIQUE/SORT function, with each of those items separated by comma space.
I then need to split the string into separate cells which I do using the TEXTSPLIT function. Because the concatenated string had comma-space between each location, I specified comma-space as the delimiter. What this does is looks at the string and splits it into multiple cells across a row based on the position of comma-space. The comma-spaces are removed, leaving the individual items.
Now I need to use the TRANSPOSE function to flip the individual items generated by TEXTSPLIT so that they are listed down a column instead of across a row
This is the GROUPBY formula that I used to generate the data (in this example it was revenue per product for a fictitious ice cream company:
=IF(OR(J2="All Locations",J2=""),GROUPBY(Sales2[[#All],[Flavour]],Sales2[[#All],[Revenue]],SUM,3),GROUPBY(Sales2[[#All],[Flavour]],Sales2[[#All],[Revenue]],SUM,3,,,Sales2[Customer Location]=J2))
If J2 (the cell containing the drop-down) contains the words “All Locations” or J2 is an empty cell, use a version of GROUPBY without the filter argument otherwise use a version of GROUPBY with the filter argument.
Download a copy of the file(s) used in this video: https://share.zight.com/9ZuJLoDp