top of page
Writer's pictureZach

Formula: Return Column X, Sorted by Column Y

Return a filtered single-column data set sorted by data in a different column. Two examples include a Sales Scoreboard and a prioritized Action Items list

Who is your top salesman? Create a simple, beautiful dashboard with Sheets

The other day I walked in to the offices of a company I used to work at and saw a Sheets-based Sales Leaderboard being displayed on monitors. "That's cool" me-thinked. I decided to recreate it.


Real-time importing of data from Salesforce to Sheets is easy - you can use the Data Connector for Salesforce plug-in, or a software like Zapier.


The challenging part is creating the simple visual display. I want to minimize excess calculations and manual filtering, and account for potential complexities (what if two people are tied? What if the number of sales people decreases?)


Here’s the formula I used:


TRANSPOSE ( INDEX ( TRANSPOSE ( SORT ( multicolumn_range , sortColumn_number , TRUE ) ) , returnColumn_number ) )

This one’s a little hacky (transpose and then re-transpose), but it works great. No need to create a “rank” value for each row, or anything like that. Instead you simply grab the whole data table (the “multicolumn_range”) and identify which column number you want to sort by (the “sort_column_number”) and which you want to return (the “return_column_number”).


(note: you can change “TRUE” to “FALSE” in this formula to make the sort ascending rather than descending)


Here it is in action:



For fun, let's look at a more complicated example... Say your team uses a tab called “all_actions” to dump in activities that need to get done. In that tab, they assign a due date, and they indicate status as either “open” or “complete”. For your team call, you want to look at just the 10 most immediate and open actions. The foundation of this formula is the same, with some added clauses. You use an ARRAY_CONSTRAIN to limit the output to only 10 results. And you add FILTER criteria to the multi_column_range so as to look at only "Open" or "Complete" actions.



Thanks for reading!


30 views0 comments

Recent Posts

See All

Comments


bottom of page