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

Question

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.

Answer 1

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.
 * @see https://developers.google.com/apps-script/guides/triggers#onopene
 */
function onOpen(e) {
  // Add a custom menu to the spreadsheet.
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
      .createMenu('Custom Menu')
      .addItem('Build TOP 5', 'buildtop5')
      .addToUi();
}


function buildtop5() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  // 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][0] == "Symbol"){
      rowNumbers.push(i)
      // Logger.log("DEBUG: i:"+i+" data:"+data[i][0])
    }
  }
  // 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

---

Comments

• the script determines the last row of data on the sheet, and dynamically identifies each "data group".
  • Add more rows: the script will adjust
  • Remove rows: the script will adjust
  • Vary the number of rows in each group: the script will adjust.
• this is set to run from a Custom menu. But there are other options that you could use to trigger the script.
• Existing formulas are deleted, and the entire formula column is rebuilt.
• 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.
  Example
• 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)