Hey folks, I’m stuck with an Airtable problem. I’ve got this table with 17 columns of data. What I’m trying to do is pick out the 5 highest values across all these columns and put them in a new table.
I tried using the MAX formula, but it only gives me the single highest value. I was hoping for something like Excel’s LARGE function, but I can’t find anything similar in Airtable.
I managed to do this in PostgreSQL with a query that uses cross join lateral and some window functions. But I’m totally lost on how to achieve the same thing in Airtable.
Has anyone figured out a way to do this? I’m open to using formulas, scripts, or any other method that could work. Really appreciate any help or ideas you can throw my way!
I encountered a similar challenge and discovered a solution using Airtable’s Automations. In my setup I created a separate table to store the top five values and configured an Automation to trigger on record creation or update in the main table. The Automation then executes a script that gathers all values from the 17 columns, orders them in descending sequence, and extracts the top five. The resulting values are then inserted or updated in the new table. Although the JavaScript coding can be a bit challenging, Airtable’s documentation is quite helpful.
I’ve dealt with this exact problem before, and it’s a tricky one in Airtable. Here’s what worked for me: I created a rollup field that concatenated all 17 columns into a single text string, separating values with a delimiter. Then, I used a combination of SPLIT() and SORT() functions to break that string back into individual values and sort them. Finally, I used SLICE() to grab the top 5.
It’s not the most elegant solution, but it gets the job done without needing external scripts or APIs. The formula ends up being pretty long, but once it’s set up, it works automatically. Just be aware that if you have a lot of rows, this method can slow down your base a bit. Let me know if you want me to share the exact formula I used – I’d be happy to break it down step-by-step.
hey lucasg, i’ve run into similar issues. airtable’s not great for complex data manipulation like this. have you considered using the airtable api to pull data into a script? you could process it there and push results back. might be overkill, but could work if you’re comfortable coding. just a thought!