Excel help needed
July 15, 2006 10:42 AM   Subscribe

How do I work with (merge) these 2 Excel Spreadsheets?

I have imported data from a non-functioning address book into Excel. I've been able to find all the data I need and have a plan to get it into my new address book program. My data was in, and needs to be put into, different categories (e.g. customers, suppliers, employees). Records can be in more than one category. Problem is my Excel skills don't stretch far enough to figure this out...

- has all the name, address, phone, etc information, with a unique record identifier

- indicates which categories each record belongs in, so can therefore have more than one entry for each unique record identifier

File#1: Record #1234, Joe Smith, 99 Main St, 555-7777
File#2: Record #1234, category1
Record #1234, category2
Record #1234, category3

How do I link or merge the two files? I don't care whether I end up adding the categories into new columns in File#1, or adding the address info onto each row in File#2. From either of those situations I know how I'll manipulate the data into a format that I can import into my new application.
posted by valleys to Computers & Internet (5 answers total)
If I'm understanding the question, you have a one-to-many relationship between Addresses and Categories (ie, one Address, "Joe Smith", can have multiple Category attributes). A "merge" of these files will result in either (a) a file with one entry per Address with multiple Category columns, or (b) a file with one entry per Address-Category pair, with redundant Address information.

Which of these formats meet the requirement of the "new application?"
posted by SPrintF at 10:52 AM on July 15, 2006

Response by poster: SPrintF - it doesn't matter to me whether I end up with a) or b) from your question. I can see how I'll get from either situation to where I need to be in the new application.
posted by valleys at 11:02 AM on July 15, 2006

Best answer: valleys:

This sounds really easy. I'd do it with the VLOOKUP function. You can leave it as two separate files or simple cut and paste the data from File 2 in to a new tab in File 1.

I'll email you a sample I did at the address in your profile.
posted by mullacc at 11:14 AM on July 15, 2006

I agree with mullacc. This is definitely what VLOOKUP is for. It can be kind of tricky if your data isn't in the right format sometimes, but it's very useful. The Help file for Excel should allow you to figure it out.
posted by gemmy at 11:33 AM on July 15, 2006

Response by poster: Thanks mullacc. It wasn't exactly what I needed, but I got it to work. I just responded to your email. Much appreciated.
posted by valleys at 11:57 AM on July 15, 2006

« Older Where to travel from Stockholm for 2-3 days?   |   Why won't amazon.com load in Firefox? Newer »
This thread is closed to new comments.