Search code examples
google-sheets

Copy Data from one column and past at the end of another column

I am trying to find a way to copy all newly added data from column A to a different column. Ideally it would be great if it will only copy new unique data to the new column but if this is not possible, it can copy and past but then id need to add another step to remove duplicates.

Here is an idea of what it needs to do:

Sample

Solution

  • You may try:

    =QUERY(A2:B, "Select A, SUM(B) Where A is not Null Group by A Label A 'Name', SUM(B) 'Amount'")

    OUTPUT

    image

    UPDATE

    Google Sheet formulas tend to recalculate whenever new data is added in the spreadsheet.

    To get the unique names without compromising their location in Column A, you may use:

    =UNIQUE(A2:A)

    To sum the amounts in Column B, you may use:

    =ARRAYFORMULA(IF(D2:D="", "", SUMIF(A2:B, D2:D, B2:B)))

    OUTPUT

    OUTPUT

    Alternatively, you may also use Google Apps Script for this. To make it work, paste the code on the Extensions > Apps Script of the Google Sheet.

    This is an example script:

    function onEdit(e) {
  var rg = e.range;
  if (rg.getColumn() == 1) {
    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var vl = sh.getRange(1, 1, sh.getLastRow()).getValues();
    var uv = [];
    vl.forEach(row => {
      if (uv.indexOf(row[0]) == -1) {
        uv.push(row[0]);
      }
    });
    sh.getRange(1, 4, uv.length, 1).setValues(uv.map(v => [v]));
  }
}

    OUTPUT

    OUTPUT

    Note: The script does not add the sum of Amount since this is only a suggestion. If needed, it can be added as well.

    If you'd like to sum the Amount, you may use:

    function onEdit(e) {
  var rg = e.range;
  if (rg.getColumn() == 1 || 2) {
    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var vl = sh.getRange(2, 1, sh.getLastRow(), 2).getValues();
    var uv = {};
    vl.forEach(row => {
      var nm = row[0];
      var amt = row[1];
      uv[nm] = uv[nm] ? (uv[nm].amt ? { amt: uv[nm].amt + amt } : { amt: amt }) : { amt: amt };
    });
    var op = Object.keys(uv).filter(nm => uv[nm].amt != undefined).map(nm => [nm, uv[nm].amt || ""]);
    sh.getRange(2, 4, op.length, 2).setValues(op);
  }
}

    OUTPUT

    OUTPUT

    REFERENCE