Combining data from two spreadsheets

[symphonic]

I have two spreadsheets. They share data in four columns (first name, last name, title, company). One spreadsheet also has an ID number column, the other has a Business Type column.

The rows don't synch up (there are 32,000 rows). I need to combine these spreadsheets into a single document so that each row has all of the relevant data--I need to match on first name, last name, title, and company, and then add either the business type or the ID number based on the matches.

Can anyone help? I'm totally new to macros, I've worked with formulas, and I don't have MS Access.

Thanks!
Steve

 

[AlisaA]

Just an idea, but if this is a one-time exercise (e.g., to clean up 2 existing lists), maybe try a simple, if somewhat inelegant, approach?

Copy the like data from your 2 worksheets (First, Last, Title, Acct Name) into one massive long list on a 3rd sheet (got 1M+ rows in Excel 2010, so no worries about it being too long!). The use the Data/Remove Duplicates on that data range, selecting all 4 columns.

That will give you one master list of people/accounts. You could then do a lookup from one sheet for Business Type and the other sheet for Contact ID. You may need an extra column to concatenate the 4 main columns to make sure you pickup the correct line, but you should end up with one combined table as a result.

That macro is a nifty one, particularly if you are having to do this repetitively, but the simple approach might do the trick for a 1-shot deal.

In terms of the macro error, check the actual name of your tab. If it is not EXACTLY "Incl Bus. Type", you will get an error on that line.

Anyway, that's my $0.02 worth (that may not even be worth that... ). Have a great holiday!

 

[symphonic]

Thanks, AlisaA.

I've tried some copying/sorting/deduping, but didn't get to where I needed to be. Will try again to work through your suggested steps. This is a one-time deal, BTW.

Regarding the Macro, when you refer to the tab name in your comment, are you saying the worksheet tab (at the bottom of the worksheet) should be named what's in the Macro, or should it be the column title, or something else?

Please excuse my ignorance.

Best,
Steve

 

[hiker95]

symphonic,

What are the actual worksheet names containing your raw data?

 

[symphonic]

WOW! I think it worked. Need to check the data to make sure it's all accurate, but I think it worked.

Hiker95, I renamed the worksheets based on AlisaA's suggestion, and that seemed to do the trick.

You guys are amazing. Can't thank you enough.

Steve