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. Watch the videos. The process is broken down into the different steps.
I hope this helps.