How to do non adjacent selection for multiple rows in MacOS Numbers

Question

Level 1

4 points

How to do non adjacent selection for multiple rows in MacOS Numbers

Environment: macOS Sierra ver 10.12.3, Numbers ver 4.0.5(4052)

I have csv file with large number of rows, and my intention is to delete all rows containing certain keyword.

First I use filter to display only rows with certain word of a specific column, then the problem comes: how to select multiple non-adjacent rows without command+click each row?

I made mistake at the first time by simply drag and selection, it turned out to delete many additional interval rows(because it is adjacent selection).

Here is website I am referring:

https://support.apple.com/kb/PH23761?viewlocale=en_US&locale=en_US

Here is part of the description:

"

Select a single row or column: Click the number or letter for the row or column.

Select multiple rows or columns: To select adjacent rows or columns, click the number or letter for the first row or column, then drag a white dot to encompass the rows or columns you want.

To select nonadjacent rows or columns, Command-click any row numbers or column letters. "

Because the number of rows to delete is large(hundreds), absolutely it is a time-consuming and boring work to select one by one, so is there any better way to do that? Thank you in advance.

MacBook Pro with Retina display, macOS Sierra (10.12.3)

Posted on Apr 5, 2017 10:32 AM

Reply

Answer 1

To_Mi

Level 4

1,202 points

Apr 6, 2017 10:41 AM in response to PannicYell

Sorry I missed the point.

How about this?
Add a column before column A.
Fill them with sequential number starting 1 with autofill feature. ( set first two cell, select those two cell, drag the yellow point on bottom edge down to the bottom ) [https://help.apple.com/numbers/mac/4.0/index.html#/tan90d4d2a40]
User filter to show only rows to delete
Change the value of column A for the rows to 0.
Turn filter off.
Sort on column A.
Delete rows those value on column A is 0. ( the rows should be gathered at top of the table )
Delete column A if necessary.

If you are familiar with UNIX command in Terminal, following steps can delete the rows (lines) with keyword in any column.

Open terminal
Type "printf 'g/keyword/d\nwq\n' | ed -s " ( without double quotes, a space as the last character.
In Finder, drag-and-drop the csv file to the Terminal window. The file path should be entered.
Return to the Terminal window, and
hit return key

mbp3:tmp 54 $ cat data.csv

1,2

2,3

3,4

4,2

3,1

4,5

mbp3:tmp 55 $ printf 'g/4/d\nwq\n' | ed -s data.csv

mbp3:tmp 56 $ cat data.csv

1,2

2,3

3,1

mbp3:tmp 57 $

# The "cat" command shows the content of the file.

Reply

Answer 2

To_Mi

Level 4

1,202 points

Apr 5, 2017 10:56 AM in response to PannicYell

1. Use "Filter" to show only rows to delete.

https://help.apple.com/numbers/mac/4.0/index.html#/tan28bd63a8a

In the Filter pane, select the column to use for filter, choose text>contains, then type in the keyword.

2. Select all rows and delete them.

3. Disable Filter ( untick the checkbox ) to see the remaining rows.

Reply

Answer 3

Apr 6, 2017 10:54 AM in response to PannicYell

I would solve this using the following:

1) add a column to hold a temporary ID to allow restoring the original sort order

2) add a column that marks rows to be deleted

3) sort by the column in (2)

4) select the contiguous rows and delete

5) sort by the column in (1)

like this. Here is a table of names:

Add two columns for the "working columns":

In column A add the formula:

A1=row()

select cell A1, copy

select cells A1 thru the end of column A, paste

select column A copy

select the menu item "Edit > Paste Formula Results"

in column B add a formula to detect whether the text "John" is in the name:

B1=IF(LEN(SUBSTITUTE(C1,"John","",occurrence)) < LEN(C1), "DELETE", "")

fill down

your table should look like this:

now sort by column B to get all the items to delete in a contiguous range,

select the rows, and delete

now sort by column A to restore the original sort (if you want)

optionally remove columns A and B

Reply

Answer 4

SGIII

Level 8

37,604 points

Apr 6, 2017 6:20 AM in response to PannicYell

Here's another easy way, using filters:

1. Set up a filter on the name column for Test .... does not contain:

2. Select the visible cells in the filtered table and command-c to copy.

3. Click once in the cell of a new table and command-v to paste. The new table will contain all the names except those with John.

4. Delete the original table that has the names containing John.

SG

Reply

Answer 5

PannicYell Author

Level 1

4 points

Apr 5, 2017 11:03 AM in response to To_Mi

Thank you for replying.

But the problem lays in, how to do non-adjacent row selection. After applying filter I got all the rows with the keyword, but there are not continuous rows. If I do drag selection it will select all the interval rows.

Suppose the first filter result is row #3, last #97. The adjacent selection will select all rows from 3 to 97... Actually I only want to delete certain rows not the continuous rows in the whole interval.

Reply

Answer 6

PannicYell Author

Level 1

4 points

Apr 5, 2017 11:22 AM in response to To_Mi

Just for example,

I have 100 rows data (only one column) of people name.

And I want to delete the records of whose name is "John". Suppose tracing the data by row from 1 to 100,

the first "John" appeared at row #50, the last appeared at row #98. And interestingly, the there is only one person whose name is "Joseph" laying between row #51 to #97. That meaning we got 48 rows with name "John".

If I simply select(adjacent) all the rows, delete, the record of poor "Joseph" will be deleted too.

This is one problem I found when handling csv files, the adjacent row deletion will delete, in the case above, rows from 50 to 98.

I can do non-adjacent selection by using command + click 48 times. but when the number comes to hundreds, thousands. I would search better, smarter ways.

The smarter way is what I am looking for.

Thanks for your concern.

Reply