grouping duplicate rows while the original sort remains intact

Question

Say I have Account Numbers, Sub-account Numbers and values in a google spreadsheet where there can be duplicate Account Numbers with different sub-acc numbers:

acc no / sub acc no / val
2      /     5      /  6
3      /     10     /  8
4      /     9      /  2
2      /     14     /  1

After I sort the whole sheet by the third column, values, it's gonna look like this:

acc no / sub acc no / val
3      /     10     /  8
2      /     5      /  6
4      /     9      /  2
2      /     14     /  1

So, what I want to do is to group all meters under the same accounts while having the original sort remain intact:

acc no / sub acc no / val
3      /     10     /  8
2      /     5      /  6
2      /     14     /  1
4      /     9     /  2

Seems like after sorting it by values, I need a script that loops through the account No column, for each cell it will check the remaining account numbers, and if there's a matching duplicate one, it's gonna get moved up.

This is what I could come up with so far:

function group(){

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  // find how many accounts we have 
  var Avals = sheet.getRange("A2:A").getValues();
  var Alast = Avals.filter(String).length;

  for (var i = 0; i < Alast-2; i++) {
    if(Avals[i].toString() == Avals[i+1].toString()){
      continue;
    }
    for (var j = i+2; j < Alast; j++){
      if(Avals[i].toString() == Avals[j].toString()){
        //move
        break;
      }
    }   
  }
}

Thanks in advance!

You need a script. What have you tried? I recommend writing a sort function for a Javascript array: Here's the reference page on MDN. Arrays that come from reading the values on a google sheet are "2D", e.g. [ [r1c1, r1c2, ... r1cN], [r2c1, r2c2, ...r2cN], ... [rNc1, ... rNcN] ] See also stackoverflow.com/help/how-to-ask — tehhowch
@tehhowch I know I need a script but I haven't tried anything since I have no experience in google apps script. Sorry. — drillau
You are expected to make an effort. This likely requires you to complete a tutorial or 3, or even take an introductory programming course. — tehhowch
@tehhowch I don't even have a lot of programming experience, I didn't know that this platform only helped people who know programming but got stuck. — drillau
You don't have a specific question. You have a request for work to be done for you. Those are very different. — tehhowch

TheMaster TheMaster · Accepted Answer · 2018-04-25T21:44:54

This is a sample script. Untested. Assumes Sheet is already sorted by C column.

function group(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // find how many accounts we have 
  //get A2:C range& values
  var rng = sheet.getRange(2,1,sheet.getLastRow()-1,3);
  var val = rng.getValues();
  var data =[];//output array
  var k=0; //data array new row count
  //loop through existing rows
  for(i=0;i<val.length;i++){
    //if A is not empty, new k row data array = original row.
    if (val[i][0]) { //value check
      data[k] = val[i];
      k++; //increment k to next row
    //Loop through rows after current row to find next  similar A value,if any
    for(j=i+1;j<val.length;j++) { 
      if (val[i][0] == val[j][0]) { //if one of the folllowing row's A value is == current A value
        data[k] =val[j].slice(0); //new k data array = j row
        k++; //increment k
        val[j][0] =''; //set  the duplicate  j row's A value to null to skip during value check 
      }
    }
   }
  }
//Logger.log(data);
sheet.getRange(2,1,data.length,data[0].length).setValues(data);
}

grouping duplicate rows while the original sort remains intact

1 Answers