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

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

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

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