9: Using 'Find' - the magic of 'Mark all'

If you scroll back a way, you'll find my unbelievably bad spreadsheet of 'books on my coffee table'. Well, here it is again, this time to show the power of 'Mark all'.

We open the find window with F5. 

So: I've looked for books with the publisher 'Penguin'. (Note that I've used \tPenguin\t, which means 'tab Penguin tab'. Otherwise, I might accidentally mark a book with the title 'I love my penguin', which isn't what I want.) 

And there they are - two books which match \tPenguin\t, marked with a little triangle called a 'bookmark'.

Now I want to do something with these bookmarked lines. Here's what I can do:

Cut the bookmarked lines 

Copy the bookmarked lines 

Delete the bookmarked lines

I want to copy them to a new document to see them more closely. I go here:

I open a new document, and I paste my bookmarked lines:

It turns out that both my Penguin books are by PG Wodehouse. Now I can look over the data, modify it, whatever I want. 

The only thing I can't do is modify the data and put it back in it's original place. Bookmarking only works in one direction. If I wanted to modify the records and then put them back in the original file, I would have to cut them from this document and paste them onto the end of the original.

Who's reading the spreadsheet?

There are two classes of 'thing' that use spreadsheets:

1. People

2. Computers

Once there is data in a spreadsheet, there are some jobs that only the person can do (e.g. type in new data), and some that only the computer can do (e.g. apply a formula automatically). The things that both can do are the ones to pay attention to when designing data structures. That's where the person's care for the computer's limitations have the most impact.

Here are some examples of things both can do. The computer can autosum a string of numbers. The person, if they really want to, can add up the numbers on a piece of paper (or in their head if they're amazing). Similarly, the computer can sort by a designated column. The person, if they really want to, can do that too - it involves a lot of cutting and pasting, but it can be done.

Here's what the computer can't do:

Arrange things so they're easier for the person. 

Here's what the person can do:

Arrange things so they're easier for the computer. 

At the most fundamental level, the single most helpful thing you can do for the computer is:

Arrange your data so the computer can sort by any column.

And by implication:

Keep your data in the smallest nodes possible.

The computer isn't as smart as the person. It's not smart at all, it has no intelligence. It can only deal with thing it recognises, and that's not actually very much. Similarly, it cannot read for meaning. As far as it's concerned, it only deals with random strings of symbols. So, to make life easier for the computer, here are some tips:

1. Colours are meaningless to the computer, and you can't sort by them. Avoid colour whenever possible, or supplement it (even an extra column with the words 'red' and 'green' in it will improve things).

2. Seperate, seperate, seperate. Never have a column with 'John Smith' in it. 'John' and 'Smith are semantic chunks - one is the chunk 'first name' and the other is the chunk 'Last name'. So seperate them.

3. Use unique ids wherever you can. If all else fails and the data gets messy, you can always sort by this column to get back to the original shape.

4. Never, ever, ever write 'same as above'. Once you sort, that little piece of information becomes a) false and b) misleading. If it's the same, then copy and paste from that cell.

5. Don't merge cells, even if it looks nicer. It's a little bit nicer for people, infinitely worse for computers. Merged cells prevent sort from working.

6. The less formatting, the better. The simpler your spreadsheet is, the better the computer will be able to deal with it. 

1. What Regular Expressions do

These tutorials are intended as a guide for people who wrangle metadata.

More specifically, they're aimed at people who wrangle tab-separated metadata and have noticed that excel has it's limits.

The name 'regular expressions' (or 'regex') isn't particularly intuitive, and at first sight, regular expressions themselves look a lot like goobledegook.

They do make sense though, and with a little practice, can be very, very powerful.