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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>
>>
>>
>
>