Deep_Level_Shark Posted February 2, 2013 Share Posted February 2, 2013 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 ? Link to comment Share on other sites More sharing options...
0 laycat Posted February 2, 2013 Share Posted February 2, 2013 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. Link to comment Share on other sites More sharing options...
0 Deep_Level_Shark Posted February 2, 2013 Author Share Posted February 2, 2013 Just matching on Location? Yes. 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 Either way, you can do this easily using the match function. How ?? Link to comment Share on other sites More sharing options...
0 Deep_Level_Shark Posted February 2, 2013 Author Share Posted February 2, 2013 comments please Link to comment Share on other sites More sharing options...
0 PUC_Snakeman Posted February 2, 2013 Share Posted February 2, 2013 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. Link to comment Share on other sites More sharing options...
0 laycat Posted February 3, 2013 Share Posted February 3, 2013 (edited) 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 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. Link to comment Share on other sites More sharing options...
Question
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 ?
Link to comment
Share on other sites
5 answers to this question
Recommended Posts