Dear Crabby: Pouring info into Excel more efficiently

My instinct would be to use access to help this process along as it is really good at taking two sets of information (tables) and JOINing them with a common key.

Access also has really great tools for importing data in from excel. She could then export her data out of access or just link her spreadsheet back to her db.

This also has the added benefit of being future flexible, such as being able to store this data on a server and having other users access it.

But this is just my approach and I realize it may not be very helpful if her version of Office doesn't have access (shame about that product segmentation but that is a whole different topic...)

My first go at this would be to use a "Lookup()" function.  It would look up the matching "key" in one sheet and return the value from the specified column in the other sheet.

vlookup in both files would identify the values that were on one sheet but not the other.  Combining the data with vlookup to put all the common row data into one file would also help.  You can put in a conditional format too to make the highlights more obvious.

I would do it the same way as sue.

And to filter you can make an extra colum te check if the value is the same.

=IF(C1=D1;1; 0)

Then autofilter on that colum 1= duplicate; 0 unique

i am also think use of access...

Use MATCH to find the index of the key column on both sheets and then use INDEX to pull in the data you want.  MATCH returns #VALUE if it does not find a match so you can test for that, and if it's an error, just leave those cells blank.

Using VBA, you can autofilter the combined list by, say, double clicking a cell.  Or you can combine MATCH and COUNTIF with OFFSET to pull in data matching a specified value in a specified column, but the list would have to be sorted by the specified column in order to find all of the duplicates.

You may need to combine your key columns first, which will be easier if you just copy paste them into the key column of your combined sheet and then use the remove duplicates feature.  It can be done using worksheet functions, but it has the potential to be really slow and involves recursive functions; I'd recommend using VBA to automate that rather than worksheet functions, especially if your keys are not numbers.

Also, I don't feel real confident that I understand what she's trying to do.  It would help if I had some sample data.

Perhaps this will help? It's a new post about VLOOKUP by Anneliese Wirth, on the Excel blog. Here's the link:  blogs.msdn.com/.../vlookup-uhh-now-what.aspx. Its title is: =VLOOKUP (uhh...now what?).

I agree with Rosewood. However, another solution would be to save the files as .csv  files and then import them into Excel. Excel is more an accounting, spreadsheet type of a solution. Access is the way to go for data management especially involving keys for a company of 500 or less. Then you would want to try DB2 (or is it 3 now?) or Oracle.

I agree with Rosewood. Excel is more of a mathematical, accounting type application. Access is the way to go for data management. However, I would recommend converting the files into .csv format and then importing them into excel.

Thank you everyone for your comments.  I've never used Access but am eager to get started with it.  I'm also going to try all of the Excel suggestions so I see how they work..  Let the fun begin...

I have some vbscript files that do this for our accounting team. The code queries the workbook as if it were a database and performs a join on the two sheets. The results are saved to a second workbook. I've never tried, but maybe the same could be accomplished with MS Query?

Microsoft just released the perfect solution to your problem.  PowerPivot for Excel is a free 2010 add-in for Excel that lets you import and integrate data from virtually any data source, including other spreadsheets.  Once you integrate your two data sources you can extend them by adding new calculated measure, and can build pivot tables and charts based on the combined sources.  

There is also a SharePoint component called PowerPivot for SharePoint 2010 that lets you upload the file and schedule it to refresh from each of your data sources.  Users can view and interact with the report simply using their web browser.

Download PowerPivot for Excel 2010 from Microsoft here: http://bit.ly/c4RcoH