From Amazon, I exported a list of Kindle title and authors into a spreadsheet. The title and authors are separated by a blank row.
1) How do I remove even-numbered rows (which are blank) all at one time?
2) Once that is done, the row with authors will become the new even-numbered rows. How do I move the data in the even-numbered rows to Column B?
MacBook Pro 13″, macOS 12.6
There's no one-button solution to this, but two ideas come to mind.
First is a script - most likely an AppleScript - that parses the table and removes empty rows. The script could then re-walk the table moving the authors to Column B. Not too hard to do if the data is well structured.
The other option is to eschew the spreadsheet for now and open the file in a text editor that supports regular expressions. Then you can do a simple find-and-replace to find the blank links and replace them with tabs to collapse the lines. A quick throw-together came up with this:
The 'Find' part is a regular expression that finds pairs of lines with a blank line in between and replaces this the blank line with a tab. Running it again merges the lines (there's probably a better regular expression that does it in one hit, but this is a quick and dirty approach).
Once you've run it though this, you can re-open it in your spreadsheet for further processing.
Note, though, that your data does not appear to match what you say - specifically it looks to me like rows 4899 and 4901 both contain book titles, whereas your definition implies that there should be an author name in the middle.
No regular expression (at leas that I can come up with) will be able to correct this kind of situation - either the data contains pairs of title/authors, or you need a better way to identify the authors vs. titles beyond 'alternate lines'
There's no one-button solution to this, but two ideas come to mind.
First is a script - most likely an AppleScript - that parses the table and removes empty rows. The script could then re-walk the table moving the authors to Column B. Not too hard to do if the data is well structured.
The other option is to eschew the spreadsheet for now and open the file in a text editor that supports regular expressions. Then you can do a simple find-and-replace to find the blank links and replace them with tabs to collapse the lines. A quick throw-together came up with this:
The 'Find' part is a regular expression that finds pairs of lines with a blank line in between and replaces this the blank line with a tab. Running it again merges the lines (there's probably a better regular expression that does it in one hit, but this is a quick and dirty approach).
Once you've run it though this, you can re-open it in your spreadsheet for further processing.
Note, though, that your data does not appear to match what you say - specifically it looks to me like rows 4899 and 4901 both contain book titles, whereas your definition implies that there should be an author name in the middle.
No regular expression (at leas that I can come up with) will be able to correct this kind of situation - either the data contains pairs of title/authors, or you need a better way to identify the authors vs. titles beyond 'alternate lines'
Here is my final solution which worked.
Once I deleted the even rows (see above post), I used the same method to move data from the new even rows which had <Author> to column B
1. I first had to create a new blank table.
2. With the spreadsheet that had alternating <Title> <Author> rows, I used the same 1-2 method of creating an empty column, pasting "1" & "2" all the way to the bottom, Quick Filter, uncheck the appropriate number, and copied the data to the new table.
3. I did this once for <Titles> and did the same thing again for <Author>. This allowed me to paste <Title> in Column A and <Author> in Column B.
A little messy but it got the job done so I didn't have to retype titles or authors.
Here is the workaround to deleting even rows
1. In an empty column (of the first odd row), type "1" (this row has the data)
2. In the same empty column (of the first even row), type "2" (this row is empty)
3. Copy paste "1" and "2" in the same empty column all the way to the bottom of the table
4. In the drop down menu of the empty column (now filled with 1s and 2s), "Quick Filter," uncheck "2"
5. The even rows will seem to be hidden
6. Copy all data from the first column and paste in new table
7. Delete column filled with "1"
Hi gl808,
Insert a temporary column that will allow you to revert to the original order. Perhaps a formula such as ROW then Copy and Menu > Edit > Paste Formula Results to fix those values for sorting back to the original order.
Now sort by Column A
Delete the naughty rows with blanks in Column A, then sort by Column B.
Happy Numbering!
Regards,
Ian.
Filtering is an effective way to eliminate the blank lines, but you don't need to go to the expense of filling a new column with 1 & 2 values... you can just filter the existing table to eliminate blank lines...
In the Inspector, select Organize -> Filter and choose Column A.
Numbers will identify (Blank) as an option, and turning this off will hide all the blank lines.
Either way, this does the first part of the job - filter out the blank lines. It doesn't help with the second part of merging the author and title data onto adjacent cells rather than separate rows.
Sorting isn't a solution here - yes, it would collate the blank lines together, but it would throw the rest of the data out of order.
Since the OP stated that the data is in the form of:
<Title 1>
<blank line>
<Author 1>
<blank line>
<Title 2>
<blank line>
<Author 2>
Sorting the column would result in the titles and authors being merged into a single sorted list, throwing out the relationship between title and author:
<Author 1>
<Author 2>
<Title 2>
<Title 2>
Hi gl808,
Good idea to use a Filter (show only the rows that have an odd number).
Hi Dubbia,
Good idea to use sort to send those naughty blank rows to the bottom of the table. I like it.
gl808 and Dubbi, please keep posting in this Numbers forum!
Regards, Ian.
Sorting isn't a solution here - yes, it would collate the blank lines together, but it would throw the rest of the data out of order.
Since the OP stated that the data is in the form of:
<Title>
<blank line>
<Author>
Sorting the column would result in the titles and authors being merged into a single sorted list, throwing out the relationship between title and author.
It might not be good for you to select the column and then sort it, so the empty lines go to the bottom and you have your list without empty lines.
This worked. Thanks!
Deleting even rows; move data from new even rows to Column B
