Google-sheets – Copy the contents of a row to another sheet based on a specific value

filterformulasgoogle sheetsgoogle-sheets-queryregexextract

I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern:

A1   
A2  
B1  
B2   
C1  
C2 
etc.

I want to copy the rows containing all "A" values to one sheet, "B" values to another sheet and "C" to another, and so on for all other values (leaving the original data in a 'master' sheet).

Is this possible in Google Sheets?
So far the closest I have is:

=filter("Sheet1"!A:E,"Sheet1"!E1:E1000="A1")

but this doesn't work for me unfortunately

Best Answer

assuming that A1, A2, B1, etc. are values not cell references you need to fix your syntax:
```
=FILTER(Sheet1!A:E, Sheet1!E1:E=REGEXEXTRACT(Sheet1!E1:E, "A.*"))
```

if the sheet name has spaces then it would be:

=FILTER('Sheet 1'!A:E, 'Sheet 1'!E1:E=REGEXEXTRACT('Sheet 1'!E1:E, "A.*"))

also you could use QUERY:

=QUERY(Sheet1!A:E, "where E contains 'A'", 0)

Formula

=INDIRECT("DATA!A"&A1)
enter image description here

Google Apps Script

function dataSubset(data,subset) {
  var output = [];
  for(var i=0, iLen=subset.length; i<iLen; i++) {
    for(var j=1, jLen=data.length+1; j<jLen; j++) {
      if(parseInt(subset[i]) == parseInt(j)) {
        output.push(data[j-1]);
      }
    }
  }
  return output;
}

enter image description here

Formula only available in the new Google Spreadsheet

=ARRAY_ROW(INDIRECT("DATA!A"&A1),INDIRECT("DATA!B"&A1))

Example

I've prepared two example files for you:

Best Answer

Related Solutions

Google-sheets – filter or sort Google Spreadsheet based on condition

Google-sheets – How to use row values to index into another sheet

Formula

Google Apps Script

Formula only available in the new Google Spreadsheet

Example

Related Topic