Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |