0

Is there any option to automatically group rows by their values if they fit certain criteria or template? For example I want to group all iOS 14* records into one and all Android 8* into other

Plus group 5 records with smallest number of users into one.


Source data

Android 10 4259
iOS 14.6 4067
Android 11 1818
Android 9 1562
iOS 14.4.2 1016
Android 8.1.0 458
Android 8.0.0 348
iOS 14.4 341

Android 10 4259
iOS 14.6 4067
Android 11 1818
Android 9 1562
iOS 14.4.2 1016
Android 8.1.0 458
Android 8.0.0 348
iOS 14.4.2 341
iOS 11.4 1
iOS 12.1.3 1
iOS 12.4.2 1
iOS 13.2 1
Android 7.0.0 1

4
  • superuser.com would be the more appropriate place to ask this question.
    – sfxedit
    Jul 27 at 19:29
  • I’m voting to close this question because it does not appear to be about Apple hardware or software within the scope defined in the help centre.
    – Tetsujin
    Jul 28 at 6:07
  • 1
    There are several ways to do this, you might use a Pivot Table, the Query function using complex formulas. If you need further help, please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Jul 28 at 19:19
  • 1
    You can help those here to help you by sharing a link to a spreadsheet with the raw data already entered (what is shown in the post images should be sufficient) as well as a manually entered expected result.
    – Erik Tyler
    Jul 30 at 3:04
1

Your question can be resolved by using query(). There are precdeents on webapps and stackoverflow for each element of the solution but none for bringing them together into a single formula

OS USERS - iOS14 and ANDROID 8 GROUPED

Use this formula:
=query({query({A2:B9},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B9},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B9},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc")

This consists of several nested queries:

Group Android 8 Users

  • =query({"Android 8 *",query({A2:B9},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)})

Group iOS 14 Users

  • =query({"iOS 14.*",query({A2:B9},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})

Group users who are neither Android 8 or iOS14

  • =query({A2:B9},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ")

group 1


OS USERS - iOS14, ANDROID 8 AND SMALLEST 5 USERS GROUPED

Use this formula:
=query({array_constrain(query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc"),count(query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc"))-B17,2);query({"Other",sum(array_constrain(query({query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc")},"select Col1, Col2 order by Col2 asc"),B17,2))})})

This also consists of several nested quesries. To enhance flexibility, the number records with smallest number of users is treated as a variable. In this example, the number of smallest values is entered in Cell: B17

All user groups, iOS14 & Android 8 aggregated

  • =query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc")

The total number of user groups

  • =count(query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc"))
  • uses COUNT().

All user groups sorted ascending, array_constrain = 5

  • =array_constrain(query({query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc")},"select Col1, Col2 order by Col2 asc"),B17,2)

Sum the total of the smallest user groups

  • `=sum(array_constrain(query({query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.iOS 14.' and NOT Col1 matches '.Android 8.' ");query({"Android 8 ",query({A2:B14},"select sum(Col2) where Col1 matches '.Android 8.' label sum(Col2) ''",0)});query({"iOS 14.",query({A2:B14},"select sum(Col2) where Col1 matches '.iOS 14.' label sum(Col2) ''")})},"order by Col2 desc")},"select Col1, Col2 order by Col2 asc"),B17,2))
  • uses SUM()

All users, iOS14 & Android 8 aggregated, array_constrain = (total-5)

  • =array_constrain(query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc"),count(query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc"))-B17,2)

Sum of the 5 smallest User groups

  • =query({"Other",sum(array_constrain(query({query({query({A2:B14},"select Col1, Col2 where NOT Col1 matches '.*iOS 14.*' and NOT Col1 matches '.*Android 8.*' ");query({"Android 8 *",query({A2:B14},"select sum(Col2) where Col1 matches '.*Android 8.*' label sum(Col2) ''",0)});query({"iOS 14.*",query({A2:B14},"select sum(Col2) where Col1 matches '.*iOS 14.*' label sum(Col2) ''")})},"order by Col2 desc")},"select Col1, Col2 order by Col2 asc"),B17,2))})

Group2


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.