Apologies for the delay, but here's how I'd do it:
- In your third workbook, use a formula to read the values of Location, something similar to:
='[Excel 1.xlsx]Sheet1'!$A2 This will be your 'base' set of values to check against.
- Have an additional second column using match() to check if this Location exists in the second workbook. Something similar to:
=MATCH(A2,'[Excel 2.xlsx]Sheet1'!$A:$A,0) This returns either the location in the array the exact value (the third argument means 'exact') is found or #N/A if not found.
- For Code and Weight, check if the value found above is a number, if so use to OFFSET(). Code for 'Code'
=IF(ISNUMBER(B2),OFFSET('[Excel 2.xlsx]Sheet1'!$B$1,B2,0)-1,"NOT FOUND") Weight is similar.
- Once you've done this, filter your results based on either 'Found', 'Code' or 'Weight' to filter just those that were found.
I've attached the workbooks as an example:
NB1: For this to work, have all of your workbooks open, I'm unsure on your excel prowess but it's easier to construct these formulas by having them all open and the windows tiled. That way you can construct the references to external workbooks by clicking as you would any other formula references.
NB2: This would probably be neater (although not sure about quicker) to do as a macro that loops through all columns in one cell, and only grabs the values from Excel 2 if it exists. However, this seemed a little easier to try and explain.
Hope this helps.
Edited by laycat, 03 February 2013 - 10:17.