What's the quickest way to code this data?

October 16, 2012 6:32 AM

I have an Excel spreadsheet with just over 3000 sentences in column A. I need to go through looking at each one and entering some data into columns B-F. The first thing I have to enter is whether the sentence is in the past tense or present tense. If it is past tense, I can ignore it and not have to enter anything in columns C-F. I just move on to the next sentence. Is it going to be quicker if I go through the whole spreadsheet filling out column B first (past/present) and then do a second pass to fill out the rest of the details for the present tense sentences, or quicker to do it all at once for each sentence? Or is it a wash?

The features in the other columns are each things that can take a second's thought to decide on, but no more than that. For example "Is the subject a pronoun?"; "Is the verb a form of the verb to be?", etc.

Any other tips for speeding this up would be appreciated. I've already arranged my columns in optimum order, I think, through a bit of trial and error.

(Incidentally, this needs to be extremely accurate, so I can't just program in an algorithm to reduce my sentences by weeding out anything with e.g. "he" followed by a word ending in "-ed". I tried various more complicated versions of this - they all had too many errors - both false positives and false negatives. And the language is a non-standard dialect, conversational and colloquial, so I'm not having luck with automating things like subject identification either. I'd appreciate it if this doesn't turn into a discussion about that sort of automation and just assumes I'm going to be doing it all by hand.)

posted by lollusc to Computers & Internet (8 answers total)

Response by poster: (Oh, in case it's relevant, about 2/3 of the sentences are turning out to be past tense so far).
posted by lollusc at 6:33 AM on October 16, 2012

Best answer: Definitely quicker to do just column B first. Set up two macros, one that enters "past" and moves down one cell, and one that enters "present" and moves down one cell. Assign them to easy-to-reach key combinations e.g. Ctrl+q and Ctrl+w. Then you can just quickly skim down entering the past/present text quickly.

Once you've done that, use the Auto-filter to hide all the rows that have "past" in column B so it's easier to add the rest of the data.
posted by EndsOfInvention at 6:37 AM on October 16, 2012 [2 favorites]

I think I'd go through the whole sheet filling out column B, then do a quick filter to hide all the sentences that are past tense. I don't know if it would necessarily be any quicker that way, but it would give me a better picture of how much work was left to do, and I think that would help motivate me to stay on task and get done sooner.
posted by Balonious Assault at 6:38 AM on October 16, 2012

Best answer: Having done similar stuff, the best way is to do one, switch over when you get bored enough, then switch back.
posted by jeather at 8:20 AM on October 16, 2012 [1 favorite]

My initial response was going to be -- of course, fill all the B cells with "0" for past, then check each sentence and change "0" to "1" for present if required, sort/filter, and start the 'real" work.

However -- you'll have to read about 1000 sentences twice with this approach! What you're doing right now seems more efficient.
posted by prenominal at 10:20 AM on October 16, 2012

I would do each sentence at a time, because I think it is easier to do analysis when you aren't switching as often. But that may be my mind, YMMV.

(I know you said you expect to do this manually, but if you know of a qualitative analysis software that can do this I would definitely be interested in it.)
posted by epanalepsis at 12:55 PM on October 16, 2012

Response by poster: Those of you who have said "definitely quicker to do B first", what are you basing this on? Is it just instinct? I feel like there should be more general principles that apply here and some definitive researched answer out there.

I love the macro idea, by the way. I was doing 0/1 manually, but the macro with quick keys will be faster.
posted by lollusc at 3:40 PM on October 16, 2012

Based on my experience with doing these sort of tasks (but without any data), I found that I can get very good at doing on task repetitively, and so by doing one task at a time my brain has to switch "modes" less frequently. This gain is enough for it to be worth rereading some of the sentences the second pass through when you do the other tasks.
posted by vegetableagony at 2:43 PM on October 27, 2012