Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Duplicate Rows in Google Spreadsheet based on Value

I'm trying to programatically duplicate rows in a Google spreadsheet. I would like the number of times the row is duplicated to be based on one of the values in the row itself.

For example, lets say I have a table like this:

enter image description here

You can see that there are numbers in column C. The value in column C is the number of times I would like to duplicate the row. This is what the desired result would look like:

enter image description here

Technically, if the value in column C was 3, we would be duplicating the row two times.

Any ideas on how to script a Google spreadsheet to do this would be great!

Thanks!

like image 210
Brandon Avatar asked Aug 20 '14 04:08

Brandon


People also ask

How to insert duplicate rows in Google Sheets?

The Formula to insert duplicate rows in google sheets: Master Formula: =ArrayFormula(vlookup(transpose(split(query(rept(row(A2:A)&" ",F2:F),,9^9)," ")),ArrayFormula({row(A2:A),A2:E}),{2,3,4,5,6},0)) In the above example, I’ve applied this formula in cell I2.

How to find duplicate data in a row of a sheet?

To find duplicate data in a row of a Google Sheet: 1 Highlight the row by clicking on the corresponding number next to it. 2 Go to Format. 3 Select Conditional formatting. 4 Under "Format rules," select Custom formula is ... 5 Input a version of the following formula, depending on the row you've highlighted. ...

How to highlight duplicates in Google sheets using custom formula?

Select Custom formula is in the Format cells if menu. Then, enter =countif (A:A,A1)>1 (adjust the letters for the chosen column range). Choose a color in the Formatting Style section. Other methods: Use the UNIQUE formula or an add-on. This article explains how to highlight duplicates in Google Sheets using three methods.

How to copy rows to another sheet based on specific cell value?

Copy rows to another sheet based on specific cell value in Microsoft Excel. After installing Kutools for Excel, please do as follows: 1. Select the data range that you want to copy rows based on specific criteria, and then click Kutools > Select > Select Specific Cells, see screenshot: 2.


2 Answers

This is fairly simple and you should have tried it yourself. I'm sure that when you'll read the code below you'll say "Oh, of course, I could have done it easily"... right ?

function autoDup() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  for(var n in data){
    newData.push(data[n]);
    if(!Number(data[n][2])){continue};// if column 3 is not a number then do nothing
    for(var c=1 ; c < Number(data[n][2]) ; c++){ // start from 1 instead of 0 because we have already 1 copy
      newData.push(data[n]);//store values
    }
  }
  sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);// write new data to sheet, overwriting old data
}
like image 183
Serge insas Avatar answered Oct 14 '22 07:10

Serge insas


Assuming your input is on inSheet, on a different sheet you can place the following formula =transpose(split(arrayformula(textjoin(",",false,iferror(rept(inSheet!A1:A&",",inSheet!$C1:$C)))),",")) and drag it right twice and you're done. This could be bulletproofed to handle empty cells or ones with commas or repeat values of 0, but for nice data like above, it works as is.

like image 29
Jeremy Kahan Avatar answered Oct 14 '22 08:10

Jeremy Kahan