This is particularly useful when working with lists copied and pasted from withing software (like WordPress or Drupal). Perhaps you are making a list of blogs you have written or you want to check the order of a menu: often when you copy this data you get spare bits of html such as button names or tooltips pasting over, and it’s a pain to remove them manually.
Today, I had a list of 400 Durpal pages in a menu that I wanted to check the order of, comparing them to lists which I know were correct. The initial copy and paste was a real mess:
First, I removed some extra lines and things using the methods mentioned here and here. Then I pasted the content into a LibreOffice Calc sheet.
As you can see, every other line is a button title to enable a menu link – those are not needed! So lets remove them.
Add descending numbers
First, you will need an empty row at the top of the sheet.
Then put a number 1 next to the first item, in the next column over: drag or click to auto-fill the column with ascending numbers.
Add a mod
Next, click the adjacent cell in the next column over and (assuming you want to remove every other row) put in:
=MOD(top number in sequence, 2)
so in my example that was:
=MOD(B2, 2)
If you want to remove every 3rd row you could but 3, every 4th row, 4 and so on.
Again, drag or click down to autofill.
You should see 1, 0, 1, 0 if you are selecting every other row, or 1, 2, 3, 1, 2, 3 if you are selecting every 3rd row.
Filter
Finally, select the whole column, and go
Data > AutoFilter
Click yes if it asks “Do you want the first line to be used as column header?”
You will now see a little drop-down in the first cell of that column:
Click the drop-down and uncheck the line number you want to delete: in my case this is zero, in the image above “Tables” (which I want to keep) has a 1 in row C but “Enable Tables…” (which I want to lose) has a zero. So I un-check zero.
Now all the ones are visible but the zeros are gone!
If you made a mistake, you can change the settings on the auto-filter.
Enjoy!
I can now check if my items are in order – and I can already spot an error, so I am glad to checked!
