# Group by to filter data **URL:** \ **Category:** Feature Requests\ **Created:** [May 20, 2020, 7:32am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467 "2020-05-20T07:32:28Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![dharmel](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dharmel/32/7494_2.png) [@dharmel](https://community.glideapps.com/u/dharmel)\ **Post date:** [May 20, 2020, 7:32am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/1 "2020-05-20T07:32:28Z")
Hello, Here is my app : [https://nduqe.glideapp.io/](https://nduqe.glideapp.io/) It aimed to list all kids activities in our region. However, every organizator has his own “ages range” and the filters is thus not really friendly. Anyone has an idea how to solve that ? Is there a function to use in the googlesheet to improve that ? Thanks !! [2020-05-20 09\_31\_35-Films et TV|250x500](https://community.glideapps.com/uploads/short-url/iXkHPZdD3Ol1XyhxB83kLClBu9L.png) ![2020-05-20 09_31_59-HappyKids - Google Sheets et 5 pages de plus - Travail - Microsoft​ Edge](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/c/cf45dfb4fdc5b47d4a1b359b15b9db1cf427afaa.png) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 20, 2020, 7:54am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/2 "2020-05-20T07:54:16Z")
Hi dharmel, How would you want the filter to look like? A user selects a minimum age and a maximum age for their own, then all matching program appears? ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [May 20, 2020, 11:18am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/3 "2020-05-20T11:18:39Z")
@dharmel What are you trying to achieve? What is the specific behavior you would like to see in your app? A few ideas: - The data in column C is aligning left and right. Unless you did this manually, this makes me think some of the cells are formatted to text and others to number. It’s good practice to have clean data and you could set a data type to the column and clean up the data. - Depending on what you are trying to achieve, you might want to break up the information in column C: minimum age (one column), maximum age (another column). This is because in each cell in column C, you actually have two pieces of information. - You can use the [unique() function](https://docs.glideapps.com/all/reference/using-sheets/functions/unique-create-categories) to create categories of your items. - You can use the [filter() function](https://docs.glideapps.com/all/reference/using-sheets/functions/filter) to create smaller data sets of your items. I see from your screenshot that you set a filter. The filter() function does the same, as a function. - You can use a [template column](https://docs.glideapps.com/all/reference/data-editor/computed-columns/template-column) in Glide’s data editor or the concatenate() function in GS to then string your data together and create something nice like “8 - 18 ans”. - If you do decide to break up column C (again it depends on what you are seeking to achieve), you might consider using the right(), left(), find() and len() functions to save time. Or maybe the split() function by using a space as the delimiter. ---
**Author:** ![dharmel](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dharmel/32/7494_2.png) [@dharmel](https://community.glideapps.com/u/dharmel)\ **Post date:** [May 20, 2020, 12:42pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/4 "2020-05-20T12:42:32Z")
Hello @nathanaelb , thanks for quick response ! Each organizator gives his range of ages (e.g : 4-7 years, 2-5 years, 2.5 - 4 years…) What I would like to appear in the filter is 4 years, 5 years,… Depending on that, it will select all activities where the age is included… How do think I should proceed ? Rgds ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [May 20, 2020, 7:52pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/5 "2020-05-20T19:52:12Z")
Hi @dharmel I would do what I suggested: - split the string of text that your organizers submit thereby creating a range for each organizer with a min and max value (“4-7 ans” becomes min=4 and max=7) - then in your filter or visibility settings, set your property “Age” to be greater than min and smaller than max. These are just ideas and I would adopt a trial and error approach. ---
**Author:** ![dharmel](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dharmel/32/7494_2.png) [@dharmel](https://community.glideapps.com/u/dharmel)\ **Post date:** [May 20, 2020, 8:21pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/6 "2020-05-20T20:21:11Z")
@nathanaelb Not sure to understand how to split it in my googlesheet… how do you define a range? I tried with a string “4;7” in one column or 2 columns, one with min and one with max. Then I defined a filter based with condition on min/max. But how it helps displaying only min and max ages of all activities. Did I missed something? ![2020-05-20 22_19_26-HappyKids · Glide · Create Apps from Google Sheets et 6 pages de plus - Travail](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/3/3ea9ba0bbe33bbc37a2aa431b52677cfa6ae8791.png) ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [May 20, 2020, 10:12pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/7 "2020-05-20T22:12:25Z")
@dharmel I’ll have a look this weekend, I cannot before, my apologies. Another idea: in the search bar on the homepage of the forum, search for “filter on a range” or “filter on intervals”. Your use case might already have been discussed in another topic. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 20, 2020, 11:56pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/8 "2020-05-20T23:56:38Z")
I will try to take time today to get some random data and explain the idea. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 21, 2020, 2:54am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/9 "2020-05-21T02:54:41Z")
This is the way to take the min and max age values from the input. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/7/7c574a37843830a646e1936a41f258e5f737bc87.png) Formula in column C: ``` ={"Min age";ARRAYFORMULA(IF(B2:B<>"",VALUE(SUBSTITUTE(LEFT(B2:B,FIND("-",B2:B)-2),",",".")),""))} ``` Formula in column D: ``` ={"Max age";ARRAYFORMULA(IF(B2:B<>"",VALUE(SUBSTITUTE(TRIM((SUBSTITUTE(RIGHT(B2:B,LEN(B2:B)-1-FIND("-",B2:B)+1)," ans",""))),",",".")),""))} ``` However, I encountered a problem with the logic when working with filter comparisons. As of now, we can’t directly compare a value to another column’s value. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/1/189cab64cd46516d043ca502ed0abb732bcdcd03.png) I wanted this logic to be Child age (User’s specific input) to be larger than the min age and smaller than the max age, for example, but it was not achieveable. Hope someone can chime in with another workaround based on the values taken from the age range. ---
**Author:** ![dharmel](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dharmel/32/7494_2.png) [@dharmel](https://community.glideapps.com/u/dharmel)\ **Post date:** [May 21, 2020, 8:28am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/10 "2020-05-21T08:28:05Z")
that was, was I had in mind but same problem ☹ I was thinking using inline and make a tab with ages and then make relations with activities but then the display of activities’info in cards is not really good ---
**Author:** ![dharmel](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dharmel/32/7494_2.png) [@dharmel](https://community.glideapps.com/u/dharmel)\ **Post date:** [May 21, 2020, 6:55pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/11 "2020-05-21T18:55:46Z")
@nathanaelb don’t have any magical function (as the one for geolocalisation) ? 😃 My problem : I have a column with ranges of ages for activities (differents for each organizator) What I want : Display single value in the tab filter (2years, 3 years,…) and it matches all activities Thanks in advance for your help ! ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [May 22, 2020, 3:45pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/12 "2020-05-22T15:45:00Z")
@dharmel I gave your problem some thought. Here are a few ideas. This post is long. Before reading any further, you might want to watch Glide’s two following tutorials: [Array columns](https://www.youtube.com/watch?v=x_vKwWrK9wE&t=3s) [Multiple categories](https://www.youtube.com/watch?v=SL-GekJLtTA&t=2s) **How your organizers are to input their offering** - I would force them to input a min. age and a max. age, which would define their offering. - They can input for instance “4” and “8”. You can make it pretty on the front end by confirming that they are offering “4 à 8 ans” (by using a [template column](https://www.youtube.com/watch?v=SL-GekJLtTA&t=2s) in the Glide data editor) - This is to guarantee that your data is clean and it will save a lot of headache with cleaning the data in GS. Some will write “De 4 à 8 ans”, others “4-8 ans”, others “4 - 8 ans”, some will write “14.5 ans”, other “14,5 ans”. This is a headache. - Here is a screenshot: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/4/4666e21537216ff06871a83a1ab065c26bfc6c7c.png) - “Organizer”: user input - Min age: user input - Max age: user input - Category offering: [template column](https://www.youtube.com/watch?v=SL-GekJLtTA&t=2s) in the Glide data editor. You can display this to your users to make it user friendly. I did this in GS, but it is a better practice to do it in the Glide data editor. In GS, here is the formula I created in row 2: =B2&" à “&C2&” ans". **Categorize your organizers by age category** - I would suggest creating age categories: we can then display and filter on these categories. - Let’s assume the ages of the children range from 1 to 18: you will then define 18 age categories, which you can call “Catégorie âge 1”, “Catégorie âge 2”, etc. - You might want to avoid special characters such as é or â in your headers, let’s keep it safe. - It is important that when you name your categories, the numbers start with 1 and go up 1 by 1. - You can create these columns in your worksheet. - Here is a screenshot: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/0/03c7603db1c8be6cb4997a61159fb0837281c8ab.png) - The values in columns E (“Age Category 1”) to V (“Age Category 18”) are populated automatically once the min. age and max. age are inputted by the organizer. - The GS formula in E2 is: =sequence(1,C2-B2+1,B2,1). - I do not know how to wrap the sequence() function inside an arrayformula() function. - **Edit** : I don’t know how to pull sequence() down the column. **Maybe someone else can help here.** This part is important: this would then guarantee that each new organizer is automatically assigned to age categories. Maybe you do not need this to be done automatically in real-time. **Display the organizers and then filter by age category** - In the Glide data editor, in your organizers’ worksheet, **create an array column of columns 1 through 18**. You can call it “Age categories” (for instance). - Here is the [Glide tutorial on array columns](https://www.youtube.com/watch?v=x_vKwWrK9wE&t=3s), and [here the Glide tutorial on multiple categories](https://www.youtube.com/watch?v=SL-GekJLtTA&t=2s). Both are helpful to understand categorizing your organizers by age category. - In the Glide layout editor, on your organizers tab, if you allow users to filter with the “in-app filter” feature, your users should now be able to select the ages they want to see and only the organizers that fit that age category will display. You will probably need to continue to test layout options to find something you like. I have the feeling that creating age categories and fitting organizers within these age categories might now allow you to filter your organizers by age offering. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 22, 2020, 3:59pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/13 "2020-05-22T15:59:07Z")
> [@nathanaelb](#): > > I do not know how to wrap the sequence() function inside an arrayformula() function. I don’t know if Sequence can be wrapped inside an Arrayformula, I don’t think it can, but I have written many scripts as a workaround, to copy down the formula from the row above when a new row is initiated. I will have a post about that as a point of reference for future use. ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [May 22, 2020, 4:20pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/14 "2020-05-22T16:20:49Z")
> [@ThinhDinh](#): > > I don’t know if Sequence can be wrapped inside an Arrayformula You can wrap arrayformula() around sequence() to create decimal numbers within the sequence, since the sequence() function does not allow decimal counts: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/9/9771afa5e3121f3e8c7e21890d3f0107f2d03aeb.png) What I don’t know how to do is pull sequence() down the column. 🤷‍♂️ ---
**Author:** ![dharmel](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/dharmel/32/7494_2.png) [@dharmel](https://community.glideapps.com/u/dharmel)\ **Post date:** [May 22, 2020, 6:49pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/15 "2020-05-22T18:49:53Z")
thanks a lot everyone ! It works perfectly with the secund option (1 column per age) and applying the arrayformula. You save me ! ![2020-05-22 18_48_53-HappyKids · Glide · Create Apps from Google Sheets et 5 pages de plus - Travail](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/d/db2fa45fdc77f8182b8301e2336b21a8f3e1d16b.png) ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [May 22, 2020, 7:31pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/16 "2020-05-22T19:31:23Z")
I’m glad to hear we figured it out. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 22, 2020, 11:01pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/17 "2020-05-22T23:01:46Z")
That was absolutely amazing Nathanael. Hats off. ---
**Author:** ![Gabriel\_Sobral](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gabriel_sobral/32/6513_2.png) [@Gabriel\_Sobral](https://community.glideapps.com/u/Gabriel_Sobral)\ **Post date:** [May 22, 2020, 11:10pm UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/18 "2020-05-22T23:10:15Z")
> [@ThinhDinh](#): > > I don’t know if Sequence can be wrapped inside an Arrayformula, I don’t think it can, but I have written many scripts as a workaround, to copy down the formula from the row above when a new row is initiated. I will have a post about that as a point of reference for future use. I also have a Python script that rolls-down formulas that can’t be wrapped inside an ArrayFormula. It is so useful! But maybe for that application I think Google App Script is a more reasonable, simpler way of doing that. ---
**Author:** ![StephM](https://avatars.discourse-cdn.com/v4/letter/s/f9ae1b/32.png) [@StephM](https://community.glideapps.com/u/StephM)\ **Post date:** [May 27, 2020, 9:45am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/19 "2020-05-27T09:45:57Z")
Hi guys! I am trying to add a second unit (“months”) to this age filter (currently only uses “years”) AND, if possible, I would also like to introduce ranges. Any idea how I could do this? In an ideal world, I would like my filter to look like this: 0-6 months 6-12 months 12-18 months 18-24 months 2-3 years 3-4 years 4-5 years 5-6 years 6-8 years 8-12 years 12 years+ In a less than ideal world, it could look like this: 6 months 12 months 18 months 24 months 3 years 4 years 5 years … 12 years What would you recommend? Thanks so much!! ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [May 27, 2020, 11:49am UTC](https://community.glideapps.com/t/group-by-to-filter-data/9467/20 "2020-05-27T11:49:52Z")
Hi @StephM, how are you? I believe you can achieve your “ideal world” case. Start with the end user experience in mind (the filter by age category), organize your data accordingly (your spreadsheets, your columns, the data types), and then set up the data input according (add/edit components and data types). - (Step A) The end goal is your filter. Aim to have your “Age category n” columns side by side and populated with the elements of your “ideal world filter”. Doing it by hand is your first step. Do this to make sure you get it to work in the front end. Get the UX right first before you spend time fiddling around with tables and data and formulas. - (Step B) Then, see how your users are submitting the data. Set this up and do it in such a way that once in GS or the GDE (Glide Data Editor), the data is going to be somewhat easy to exploit. - (Final step) You need to take the data from Step B, and transform that data so as to achieve the result in Step A. This might require a little “spreadsheet magic”, or creating columns and moving data around via the GDE (preferred method). - That’s it. [Make sure you meditate this post above](https://community.glideapps.com/t/group-by-to-filter-data/9467/12). Watch the videos. The process is broken down into the different steps. I hope this helps. [Next page](https://community.glideapps.com/t/group-by-to-filter-data/9467.md?page=2)