Ask Your Question

Copy rows from one sheet to another based on text criterion?

asked 2020-03-12 19:40:18 +0200

MaryB gravatar image


I have a simple question but have not understood logic since I am a beginner when its comes to spreadsheets. My question is how do I copy rows based on a string criterion from one sheet to another?

For example in Sheet1 I have thousands of rows and column A contains different names. I now want to find all rows containing the word "Foo Bar" in Sheet1 and copy the rows to Sheet2. Problem is that Sheet1 will be regular updated and rows different.

I tried to use Filter and it works fine if I use "Copy results to" to paste the result in another Sheet. However this method is time consuming since I need to this all the time with different names. How can I save filters?

Do I have to create a range first and then save it with a name and use in "advanced filters"? If so, how is this done? I think I need to create a formula expression, with for example =match but how to assign the match to sheet1???

=MATCH("FOO BAR";$A1:$A3500)

Is this the most efficient way to use MATCH or are there any other ways?

edit retag flag offensive close merge delete


A task as you describe should easily be solved with a database. There you might use a so-called SQL stastement for selecting from a database table like SELECT * FROM Table1 WHERE joke="foo bar"" ORDER BY loughliyness.

At a small scale (for rather few records) spreadsheets are often used as a kind of surrogate for "simple" databases with low requirements regarding the central DB concerns of Safety, Security, Integrity, because a user doesn't like the idea to learn about databases. However, this becomes a problem if the user also isn't exactly familiar with spreadsheets.

You may need to decide which one of the very different concepts you actually wish to study first.

How to do something like a SELECT ... WHERE ... in a spreadsheet isn't something an experienced user of spreadsheets can tell with a few words. And some of the most experienced would refuse anyway because they ...(more)

Lupp gravatar imageLupp ( 2020-03-12 21:27:33 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-03-12 22:32:49 +0200

m.a.riosv gravatar image

Please take a look on how to use Advanced filter

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-12 19:40:18 +0200

Seen: 74 times

Last updated: Mar 12