1

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.

3
  • 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?
    – Tedinoz
    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!
    – Eric M
    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.
    – Tedinoz
    Mar 12, 2022 at 3:57

1 Answer 1

0

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

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.