Jump to content
Sign in to follow this  
Followers 0

excel data

6 posts in this topic

Posted

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 ?

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

[quote name='laycat' timestamp='1359814300' post='595496692']
Just matching on Location?
[/quote]

Yes.

[quote]
is Location unique -
[/quote]
Yes

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

[b]Excel 1:[/b]
-----------
[color=#0000cd][i]Location ------Code-----Weight[/i][/color]
Paris 12 40
Germany 15 25
Africa 17 15


[b]Excel 2:[/b]
-------------
[color=#0000ff][i]Location ------Code-----Weight[/i][/color]
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 ..
[b]New Excel:[/b]
---------------------
[i]Location ------Code-----Weight[/i]
[color=#b22222][b]Paris 78 34[/b][/color]


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.
[/quote]

How ??

Share this post


Link to post
Share on other sites

Posted

comments please

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted (edited)

Apologies for the delay, but here's how I'd do it:[list=1]
[*]In your third workbook, use a formula to read the values of Location, something similar to: [code]='[Excel 1.xlsx]Sheet1'!$A2[/code] 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: [code]=MATCH(A2,'[Excel 2.xlsx]Sheet1'!$A:$A,0)[/code] 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' [code]=IF(ISNUMBER(B2),OFFSET('[Excel 2.xlsx]Sheet1'!$B$1,B2,0)-1,"NOT FOUND")[/code] 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.
[/list]
I've attached the workbooks as an example:
[attachment=326504: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. Edited by laycat

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.