We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How do you delete rows with common search string? [closed]

asked 2015-01-16 19:16:17 +0200

jspreadsheet gravatar image

updated 2021-05-27 21:56:49 +0200

Alex Kemp gravatar image

I cannot figure out how to delete row(s) that have a common string or value. A text file was imported containing a multitude of uneeded, identical lines(rows). The find/replace seems to be limited to replacing character strings only. I suspect a macro is needed but before I spend time on that I wonder if there is a simple approach?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-15 14:25:19.981329

2 Answers

Sort by » oldest newest most voted

answered 2015-01-28 09:39:16 +0200

Wildcard gravatar image

There is a simple approach. It sounds like you are mostly trying to remove duplicates—you mention unneeded, identical rows. All you have to do is:

  1. Select the data you need to remove. Or just click anywhere within the data—don't bother working to select the whole data range; if it's contiguous (no gaps), Calc will figure it out automatically.
  2. On the menu bar, go to Data -> Filter -> Standard Filter...
  3. The default criteria will show "Field name" [whatever the column heading is], "condition" as equals, and "value" blank. Choose "not empty" for the value so everything in your grid will match the criteria.
  4. Click the "More options" button near the bottom.
  5. Check the "No duplication" box.
  6. Check the "Copy results to..." box, and choose where you want the de-duped list to be copied to.
  7. Click "OK".

If the original spreadsheet had typos in it you still might have some extra rows, like a row for "Zebras" and an extra one for "Zebars", but it should be a small number of them if any and you can remove those manually.

edit flag offensive delete link more

answered 2015-01-16 20:56:03 +0200

LogicDaemon gravatar image

updated 2015-01-16 20:59:41 +0200

Instead of deleting these rows, you can copy only rows you need.

  1. First, mark rows you don't need on an unused column.
    • If all repeating lines are the same, you can use =A2="preset text to filter" to auto-mark.
    • Otherwise, you can make list of these lines and use =MATCH(A2;range_with_lines_to_filter;0).
    • And you can even create Pivot table with source lines on rows and their count on data, sorted by count, to find repeating data. And then use top lines of that pivot in MATCH of previous bullet.
  2. Then filter them out, Ctrl+A, Ctrl+V to new sheet.

Or vice versa, you can mark only lines you need, then filter them in.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-01-16 19:16:17 +0200

Seen: 22,390 times

Last updated: Jan 28 '15