Copy the contents of a row to another sheet based on a specific value

Question

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

Answer 1

• 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)

Answer 2

You're almost there already. First, you should probably match your filter input 'range' size and 'condition' size:

FILTER("Sheet1"!A1:E1000,"Sheet1"!E1:E1000="A1")

Then we have to somehow extract the letter part of the code in column E. If it is always that simple, I'd use LEFT to grab the single leftmost character:

FILTER("Sheet1"!A1:E1000,LEFT("Sheet1"!E1:E1000,1)="A")

If you may end up having multi-letter codes like AC1, AC2… you could extract the leading letters instead with, say, REGEXEXTRACT:

FILTER("Sheet1"!A1:E1000,REGEXEXTRACT("Sheet1"!E1:E1000,"^[A-Z]+")="AC")