MetaChat - Boring Ass Work Question

MetaChat is an informal place for MeFites to touch base and post, discuss and chatter about topics that may not belong on MetaFilter. Questions? Check the FAQ. Please note: This is important.

23 August 2006

Boring Ass Work Question At the risk of putting myself - and all of you -to sleep, I need some help with Excel.→[More:]I have to compare two guest lists (lets call them "a" and "b"). List A has about five-hundred names, List B about six-hundred. It's my job to compare the two lists (by this afternoon, with more names being added as we speak) and make sure there are no doubles, etc. List A has a different format for the fields (first and last name are together in one column, whereas they are seperated in B). I have to search by name because sometimes the same people are listed twice under different categories, companies, etc. What the fuck should I do, other than stab out my eyeballs? Is there some secrety way of doing this? Because as I see it, right now I have to go through and search for every name from B individually on A?
kill me.

post by: SassHat at: 09:05 | 9 comments

No, this is easy.

1) Concatenate list B so that both names are in the same cell.

If first is in column A, last in column B, in column c you want

=concatenate(A1,B1)

2) Run a match function against them. So now you have first_last for list B in column C. Copy and paste list A into column D. Then in column E put

=match(c1,d$1:d$600,0)

Then copy this down for the length of the list. If a name exists in both lists, it will return a number (1,2,15, etc.). If a name exists only in C, it will return #N/A. Look for the numbers and delete accordingly.

(IM me if you want more help or this wasn't clear: Fuzzhead0783)

posted by Fuzzbean 23 August | 09:29

vlookup.

posted by matildaben 23 August | 09:33

instead of using the CONCATENATE function, you could do this:

=A1&" "&B1

also, you could try using a PivotTable. those do come in handy for the "quick and dirty" data analysis stuff.

also, tell whoever put those lists together to use access next time! :)

posted by syntax 23 August | 09:35

Sorry for the length, but couldn't resist. ≡ Click to see image ≡

posted by seanyboy 23 August | 09:41

1. Put the two lists into the same format by catenating B's first and last names.

2. Add all names from "B" to the "A" list, making one list.

3. Sort the one list. Anywhere you see the same name twice in a row, it's (potentially) a dupe. Optionally, write a formula to detect this.

4. But remember, there may be more than one "Jim Jones" or "Joe Nguyen".

5. Note that this method is not particular to Excel; anytime you want to find duplicates, a decent algorithm is to sort your list and then walk it, looking for two adjacent items with the same value. You could do this using the unix sort and unique programs, or in any programming language.

posted by orthogonality 23 August | 10:10

thanks, guys. I ended up doing pretty much what Ortho described, on the advice of my coworker. My eyeballs are safe for another day.

posted by SassHat 23 August | 12:33

there are many exciting asses at my work

posted by pieisexactlythree 23 August | 12:56

Pie, are you the dirty pervert of your office?

Also, this project is a nightmare and I hate excel. and I want to go home. that is all.

posted by SassHat 23 August | 16:35

Seanyboy, that comic is hilarious. It accurately depicts my former coworkers -- both in intellect and appearance.

posted by mudpuppie 23 August | 16:40

← Voices of MeCha: || Any bunny made a "blog card?" →

HOME || REGISTER || LOGIN