1

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

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

  • 1
    Thanks, this worked for me :) – 2xtimes May 31 at 10:09
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.