# Can I use SORTN to pull top 5 values from multiple subsets of data at one time?

I have subsets of data for stock symbols combined onto one spread sheet. Symbols are Column A and Performance is Column B. I want to choose the top 5 symbols of each subset based on performance, and list those stock symbols adjacent to the data. I can use the SORTN function to do this, however I need to instruct the range of cells being sorted to stop where one subset of data ends and a new one begins. I then want the SORTN function to continue to the next subset of data and pull the top 5 performers from that new subset. And so on, until top 5 performers have been pulled for all subsets of data.

Here is a sample sheet. Feel free to play around with this however you would like. https://docs.google.com/spreadsheets/d/1sJMOpK9on9Re4OGKhFQ3bHdOHGGhqv3FPQFjmZkvgr8/edit?usp=sharing

So as you can see on the sample sheet, there's currently a formula that sorts the top 5 values from a subset, and stops where the subset of data ends. However, I need a formula that would then continue automatically to the next subset of data and pull the top 5 performers of those; and so on and so on until top 5 performers have been pulled and sorted for every subset. The one I have currently I need to manually insert at the beginning of each subset, and adjust the rows within the formula accordingly. I cannot manually input a SORTN formula and adjust the rows for the formula each time a new subset begins, because in a real-world application I will most likely be working with 100+ subsets of data.

Just to reiterate: I need the SORTN formula to automatically know to stop sorting where one subset ends and the other begins (specifically where the cell value is "Symbol"); THEN, I need it to continue onto the next subset of data and sort the top 5 performers of that new subset; I would like it to continue down the entire list, sorting top 5 performers for each subset of data.

If I could combine the data horizontally I could make a SORTN function work much easier since the number of columns are identical in each subset of data, but I am combining .CSV files using terminal on my mac, and it seems that it only combines vertically.

I'm stuck here. Please let me know if there is anything I can do, and if I need to clarify for anybody kind enough to help.

• Hi and welcome to WebApps. I suppose that there’s a good reason why the data is in groups of 10 on each page…. I was going to suggest putting the data into a Columnar format and then running a query to display the data in the row-wise layout that you’ve got here (would that raise a quota problem?) anyway, have you considered a script that inserts the relevant formula? Mar 11, 2022 at 5:40
• Thanks for responding. Forgive me, I'm only a few months into learning. "Putting the data into columnar format" sounds intriguing. If I could organize the subsets of data horizontally instead of vertically, I could use a SORTN formula to pull Top 5 performers for each subset, since I wouldn't need to worry about the varying number of rows. I am combining all CSV into one CSV using terminal on Mac. it combines everything vertically. "Have you considered a script that inserts the relevant formula?"Would you mind elaborating a bit more? A bit above my head :) Thank you for your input! Mar 12, 2022 at 2:38
• Hi Eric. Sorry, I must have skipped over but I am combining .CSV files using terminal on my mac which would rule out working column-wise. But the answer that I've given you will work with any number of rows between headers. So it's probably the easiest solution. Mar 12, 2022 at 3:57

There may be many ways to construct the formula. This answer uses a script that can be run from a Custom menu.

``````/**
* The event handler triggered when opening the spreadsheet.
* @param {Event} e The onOpen event.
*/
function onOpen(e) {
SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
}

function buildtop5() {

// specify the target sheet name
var sheetname = "testing2"
var sheet = ss.getSheetByName(sheetname)
// get the last row of data
var lastRow = sheet.getLastRow()
// create an array to hold row numbers
var rowNumbers=[]
// columnn to insert results
var columnResult = 5
//get the data
var dataRange = sheet.getRange(1,1,lastRow,2)
var data =  dataRange.getValues()
//Logger.log("DEBUG: the data range = "+dataRange.getA1Notation())

// loop through Column A and get the rows = "Symbol"
for (var i=0;i<lastRow;i++){
if (data[i] == "Symbol"){
rowNumbers.push(i)
// Logger.log("DEBUG: i:"+i+" data:"+data[i])
}
}
// add the last row to the list
rowNumbers.push(lastRow)
// Logger.log(rowNumbers) // DEBUG

// clear the column before inserting new formula
var clearRange = sheet.getRange(2,columnResult,lastRow)
clearRange.clear();

// loop through the row numbers array
// but don't use the last number (the last row())
for (r=0;r<rowNumbers.length-1;r++){
var cell = sheet.getRange((rowNumbers[r]*1)+2,columnResult)
var startrow = (rowNumbers[r]*1)+2
var endrow = (rowNumbers[(r+1)]*1)
//Logger.log("DEBUG: start row = "+startrow+" end row = "+endrow)
cell.setFormula('=SORTN(indirect("A'+startrow+':A'+endrow+'"),5,0,indirect("B'+startrow+':B'+endrow+'"),false)')
//Logger.log("DEBUG: Done")
}
}
``````

Screenshot • the script uses `setformula` to insert the sortN formula. Because the starting and ending rows of each range are know, the formula looks different to the dynamic formula used in the question.
• OP formula : `=SORTN(INDIRECT("A"&ROW()&":A"&IFERROR(MATCH("Symbol",A2:A,0)+(ROW()-2),)),5,0,INDIRECT("B"&ROW()&":B"&IFERROR(MATCH("Symbol",A2:A,0)+(ROW()-2),)),false)`
• Script formula at row#2: `=SORTN(indirect("A2:A13"),5,0,indirect("B2:B13"),false)`