Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MG
 
Posts: n/a
Default joining to worksheets or filtering not sure of correct terminology

Hi

I have two worksheets that I want to join like i can do in an access
database
The first is called Items and the Second is called Winners
Items contains about 1000 items and each row has a unique Item Number
Winners contains the same unique Item numbers but only about 100 of the
Items.
I want to join these worksheets where they intersect, that is the unique
item number, and combine into a third sheet.
I haev been abel to import both these two sheets into access and then create
a relationship between them and then export the query back to excel, but
this is taking me ages, and I woud liek to do this much quicker as I have a
bout 50 sets of data sheets to combine.
Anybody have any words of wisdom for a relative excel novice?
Thanks

--
--------------------
Michael Guthrie
ArtFusion, Ltd.
www.artfusion.com


  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi

Probably the easiest way to do this, is to copy the Winners list (the 100
item one) to the third sheet and then use a VLOOKUP function to lookup the
ID in the Items list and return the information you want.

i.e. if in Items you have
....A...........B...........C
1..ID....FName....LName

and in the third sheet (your copy of Winners) you have
.....A...........B...........C
1..ID......Date.......LName

with the ID & Date filled in, but now you want LName
then type in C2 of the Winner's copy
=VLOOKUP(A2,Items!$A$2:$C$1000,3,0)
this says, lookup the value in A2, in the Items list and return the
associated information from the third column of the lookup table where there
is an exact match.

you can then copy this down the rest of the 99 items using the autofill
handle (bottom right corner of the cell)

Cheers
JulieD
PS you don't have to copy the winners list - you can do it on that sheet
unless you want to keep that data separate for some reason.

"MG" wrote in message
...
Hi

I have two worksheets that I want to join like i can do in an access
database
The first is called Items and the Second is called Winners
Items contains about 1000 items and each row has a unique Item Number
Winners contains the same unique Item numbers but only about 100 of the
Items.
I want to join these worksheets where they intersect, that is the unique
item number, and combine into a third sheet.
I haev been abel to import both these two sheets into access and then
create a relationship between them and then export the query back to
excel, but this is taking me ages, and I woud liek to do this much quicker
as I have a bout 50 sets of data sheets to combine.
Anybody have any words of wisdom for a relative excel novice?
Thanks

--
--------------------
Michael Guthrie
ArtFusion, Ltd.
www.artfusion.com




  #3   Report Post  
MG
 
Posts: n/a
Default

Thanks Julie
This is where i was starting to look
and I manged to get the VLOOKUP to work and across separate files even, so
thanks again
Michael

--
--------------------
Michael Guthrie
ArtFusion, Ltd.
www.artfusion.com

"JulieD" wrote in message
...
Hi

Probably the easiest way to do this, is to copy the Winners list (the 100
item one) to the third sheet and then use a VLOOKUP function to lookup the
ID in the Items list and return the information you want.

i.e. if in Items you have
...A...........B...........C
1..ID....FName....LName

and in the third sheet (your copy of Winners) you have
....A...........B...........C
1..ID......Date.......LName

with the ID & Date filled in, but now you want LName
then type in C2 of the Winner's copy
=VLOOKUP(A2,Items!$A$2:$C$1000,3,0)
this says, lookup the value in A2, in the Items list and return the
associated information from the third column of the lookup table where
there is an exact match.

you can then copy this down the rest of the 99 items using the autofill
handle (bottom right corner of the cell)

Cheers
JulieD
PS you don't have to copy the winners list - you can do it on that sheet
unless you want to keep that data separate for some reason.

"MG" wrote in message
...
Hi

I have two worksheets that I want to join like i can do in an access
database
The first is called Items and the Second is called Winners
Items contains about 1000 items and each row has a unique Item Number
Winners contains the same unique Item numbers but only about 100 of the
Items.
I want to join these worksheets where they intersect, that is the unique
item number, and combine into a third sheet.
I haev been abel to import both these two sheets into access and then
create a relationship between them and then export the query back to
excel, but this is taking me ages, and I woud liek to do this much
quicker as I have a bout 50 sets of data sheets to combine.
Anybody have any words of wisdom for a relative excel novice?
Thanks






  #4   Report Post  
JulieD
 
Posts: n/a
Default

you're welcome and thanks for the feedback

"MG" wrote in message
...
Thanks Julie
This is where i was starting to look
and I manged to get the VLOOKUP to work and across separate files even, so
thanks again
Michael

--
--------------------
Michael Guthrie
ArtFusion, Ltd.
www.artfusion.com

"JulieD" wrote in message
...
Hi

Probably the easiest way to do this, is to copy the Winners list (the 100
item one) to the third sheet and then use a VLOOKUP function to lookup
the ID in the Items list and return the information you want.

i.e. if in Items you have
...A...........B...........C
1..ID....FName....LName

and in the third sheet (your copy of Winners) you have
....A...........B...........C
1..ID......Date.......LName

with the ID & Date filled in, but now you want LName
then type in C2 of the Winner's copy
=VLOOKUP(A2,Items!$A$2:$C$1000,3,0)
this says, lookup the value in A2, in the Items list and return the
associated information from the third column of the lookup table where
there is an exact match.

you can then copy this down the rest of the 99 items using the autofill
handle (bottom right corner of the cell)

Cheers
JulieD
PS you don't have to copy the winners list - you can do it on that sheet
unless you want to keep that data separate for some reason.

"MG" wrote in message
...
Hi

I have two worksheets that I want to join like i can do in an access
database
The first is called Items and the Second is called Winners
Items contains about 1000 items and each row has a unique Item Number
Winners contains the same unique Item numbers but only about 100 of the
Items.
I want to join these worksheets where they intersect, that is the unique
item number, and combine into a third sheet.
I haev been abel to import both these two sheets into access and then
create a relationship between them and then export the query back to
excel, but this is taking me ages, and I woud liek to do this much
quicker as I have a bout 50 sets of data sheets to combine.
Anybody have any words of wisdom for a relative excel novice?
Thanks








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM


All times are GMT +1. The time now is 02:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"