5 replies to this topic

[Deep_Level_Shark]

I have data in two separate Excel files.

Excel 1: column:> Location - Code - weight


Excel 2 : column:> Location - Code - weight


I want to dump data in a new Excel file for common Location in these Excel file but code and weight will be from Excel 2 only.

Is there any tool which could help to do this ?

[laycat]

Just matching on Location? Or on a combination of Location and Code? (In other words, is Location unique?)

Either way, you can do this easily using the match function.

[Deep_Level_Shark]

laycat, on 02 February 2013 - 14:11, said:

Just matching on Location?

Yes.

Quote

is Location unique -

Yes

Please see this Sample data:
----------------

Excel 1:
-----------
Location ------Code-----Weight
Paris 12 40
Germany 15 25
Africa 17 15


Excel 2:
-------------
Location ------Code-----Weight
Paris 78 34
Spain 15 25
Madrid 24 54


As you see Paris is a common location in this two excel , I'll pick it but code and weight will be from Excel 2 .

so new Excel will dump this data ..
New Excel:
---------------------
Location ------Code-----Weight
Paris 78 34


I have more than 15000 data in these excels which I need to compare and pick and have to put in a new excel through this logic. Please suggest me a workaround

Quote

Either way, you can do this easily using the match function.

How ??

[Deep_Level_Shark]

comments please

[PUC_Snakeman]

Hit the Excel button for "fx", which is insert function. Then find the function you want, and read about what inputs it needs. There's also a "Help on this function" hyperlink that can give you more info on a particular function.

[laycat]

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:
 Excels.zip   18.51K   1 downloads

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.