Ask Your Question
0

How do you delete rows with common search string?

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

jspreadsheet 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 close merge delete

2 Answers

Sort by » oldest newest most voted
0

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

LogicDaemon gravatar image

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

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
0

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

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 13,188 times

Last updated: Jan 28 '15