comparing data in two Excel spreadsheets

I have one spreadsheet (call it XLS1) with thousands of line items- the relevant data is in one column, and each box reads something like “ABC, XYZ”.

I have another spreadsheet (call it XLS2) with just a few hundred line items- the relevant data is in one column- each box reading something like “ABC”.

All of the “ABC” boxes in XLS2 have a corresponding field in XLS1 with an associated number “XYZ” in the same box. I want to put the “XYZ” numbers from XLS1 into a column in XLS2 next to the “ABC” column in XLS2.

So far, I’ve been doing “Find” in XLS1 (searching by “ABC”), and manually typing the associated “XYZ” that I find in XLS1 into XLS2. Is there an easier way?

Thanks for any help.

I think you want VLOOKUP, which searches for a specific value in a certain column, then looks across that row to return a value from another column. (I assume that works between spreadsheets.) Excel Help should give you more information.

I haven’t used the “FIND” function, but use Excel a lot - here is what I would do:

  1. Download TextPad (if you don’t have a text editor that does good stuff)
  2. Highlight the ", " from column one in sheet one - use “replace” to replace it with the regular expression " ". You may have to enable regular expressions in the replace box.
  3. You know have “ABC XYZ” the space is a tab now - and if you copy and paste it back into excel it will be two columns. This is what you want.
  4. Next to the column in sheet 2 - use “VLOOKUP” to lookup the value from sheet one and place it next to the relevant column in sheet 2. Use the option “FALSE” at the end.

There may be a more elegant way to do this in Excel, but this will work - and is simple - and will help you out in other projects in the future.

I agree with zut’s explanation – vlookup does work across separate files, but when you build the formula it will anchor all your cells; this is a good thing when defining your range, but you’ll want to un-anchor your lookup cell.

Smack that F1 button and start reading up on vlookup – it isn’t as complicated as it seems once you start using it.

Thanks for everyone’s help- I’m using the “VLOOKUP” along with “Text-to-column” to split the data at the commas, and it seems to be working.

Crap - forgot about text to column :smack: