Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
I have set up a vlook function and I am returning peoples names for a given
array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
If it's for simply looking up on demand ..
You could just apply autofilter on the percent col (DataFilterAutofilter), then use the autofilter droplist to lookup the desired percent, eg: 80%. The filtered rows will display all names with tied scores of 80% (if there are ties) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zombie0635" wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
Can you post details of how your data is laid out, and what formulae
you are using? One way of approaching this is to detect if there are duplicates (i.e. IF(A2=A1, ...) assuming column A contains your percentages, and in the THEN part of the IF adjust the range of the table that the VLOOKUP function is using, and in the ELSE part you have your normal VLOOKUP function. Adjusting the table range is done by means of INDIRECT, with a MATCH function which determines which row of the table the previous name occurs on. I'm sure you can see that the formula needs to be fitted to suit the actual data layout that you are using. Hope this helps. Pete On Oct 24, 11:55 pm, Zombie0635 wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
I think you are missing to enter a simple part in Vlookup
=Vlookup(Cell To Search,Range to Search,Col No,0) E.g. =Vlookup(A1,$K1:$N1,2,0) Just try editing your existing formula by ending it with ,0) Just a guess I am taking as you have not posted the actual formula "Zombie0635" wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
This will still get the first occurrence of 80%, not the subsequent
ones. Pete On Oct 25, 7:49 am, claude jerry wrote: I think you are missing to enter a simple part in Vlookup =Vlookup(Cell To Search,Range to Search,Col No,0) E.g. =Vlookup(A1,$K1:$N1,2,0) Just try editing your existing formula by ending it with ,0) Just a guess I am taking as you have not posted the actual formula "Zombie0635" wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks!- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
Here is my data table that is drawing data from a larger array of data.
Win % Win Loss GB 78% Doug 7 2 -- 78% Justin 7 2 -- 67% Wade 6 3 1 63% Angela 5 3 1.5 57% Ben 4 3 2 50% Kelly 4 4 2.5 Note: Because I am not profficient with Excel I use multiple tables to break up my data into smaller pieces that I can handle until I finally get to my desired results. The above table is the table I am drawing from to get my final results. Here are the formulas I use on my final table: I use the "large function" to bring over the winning % in a descending order, than I use "vlookup's" to bring over the name, wins, and losses. Of course it doesnt matter if people have the same win/loss record because numbers can be identical, but it only returns one name for the same winning %. Here is an example of my "vlookup" formula for my name: =vlookup(l35,Sheet2!$G$8:$K$13,2,false). I hope this info sheds a little more light on the subject manner, and thanks for all of your help "Pete_UK" wrote: This will still get the first occurrence of 80%, not the subsequent ones. Pete On Oct 25, 7:49 am, claude jerry wrote: I think you are missing to enter a simple part in Vlookup =Vlookup(Cell To Search,Range to Search,Col No,0) E.g. =Vlookup(A1,$K1:$N1,2,0) Just try editing your existing formula by ending it with ,0) Just a guess I am taking as you have not posted the actual formula "Zombie0635" wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks!- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
Still a bit short on detail, but here is what I think you have:
In Sheet2 cells G8 to K13 you have a table of data like you have posted. This was a bit confusing as it already seems to be sorted, but I have assumed that is just coincidence, and sorted it by name in my test. In Sheet1 cells L35:L40 you have a formula like: =LARGE(Sheet2!G$8:G$13,ROW(A1)) copied down, which gives you the percentages from the previous table but in decreasing order. You can put this formula in M35 to get the first name, as you have already indicated: =VLOOKUP(L35,Sheet2!$G$8:$K$13,2,0) In M36, however, you need a formula like this: =IF(L36=L35,VLOOKUP(L36,INDIRECT("Sheet2!$G$"&8+MA TCH(M35,Sheet2!H$8:H $13,0)&":$K$13"),2,0),VLOOKUP(L36,Sheet2!$G$8:$K$1 3,2,0)) and then this formula should be copied down into M37:M40. You can then use this formula in N35: =VLOOKUP($M35,Sheet2!$H$8:$K$13,COLUMN(B1),0) and this can be copied into O35 and P35, and then N35:P35 can be copied down the next five rows. This will give you your sorted table, and it will take account of duplicate percentages - your names should be unique, though. Hope this helps. Pete On Oct 25, 7:22 pm, Zombie0635 wrote: Here is my data table that is drawing data from a larger array of data. Win % Win Loss GB 78% Doug 7 2 -- 78% Justin 7 2 -- 67% Wade 6 3 1 63% Angela 5 3 1.5 57% Ben 4 3 2 50% Kelly 4 4 2.5 Note: Because I am not profficient with Excel I use multiple tables to break up my data into smaller pieces that I can handle until I finally get to my desired results. The above table is the table I am drawing from to get my final results. Here are the formulas I use on my final table: I use the "large function" to bring over the winning % in a descending order, than I use "vlookup's" to bring over the name, wins, and losses. Of course it doesnt matter if people have the same win/loss record because numbers can be identical, but it only returns one name for the same winning %. Here is an example of my "vlookup" formula for my name: =vlookup(l35,Sheet2!$G$8:$K$13,2,false). I hope this info sheds a little more light on the subject manner, and thanks for all of your help "Pete_UK" wrote: This will still get the first occurrence of 80%, not the subsequent ones. Pete On Oct 25, 7:49 am, claude jerry wrote: I think you are missing to enter a simple part in Vlookup =Vlookup(Cell To Search,Range to Search,Col No,0) E.g. =Vlookup(A1,$K1:$N1,2,0) Just try editing your existing formula by ending it with ,0) Just a guess I am taking as you have not posted the actual formula "Zombie0635" wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
Thanks so much that worked out tremendously. I would have never figured it
out. "Pete_UK" wrote: Still a bit short on detail, but here is what I think you have: In Sheet2 cells G8 to K13 you have a table of data like you have posted. This was a bit confusing as it already seems to be sorted, but I have assumed that is just coincidence, and sorted it by name in my test. In Sheet1 cells L35:L40 you have a formula like: =LARGE(Sheet2!G$8:G$13,ROW(A1)) copied down, which gives you the percentages from the previous table but in decreasing order. You can put this formula in M35 to get the first name, as you have already indicated: =VLOOKUP(L35,Sheet2!$G$8:$K$13,2,0) In M36, however, you need a formula like this: =IF(L36=L35,VLOOKUP(L36,INDIRECT("Sheet2!$G$"&8+MA TCH(M35,Sheet2!H$8:H $13,0)&":$K$13"),2,0),VLOOKUP(L36,Sheet2!$G$8:$K$1 3,2,0)) and then this formula should be copied down into M37:M40. You can then use this formula in N35: =VLOOKUP($M35,Sheet2!$H$8:$K$13,COLUMN(B1),0) and this can be copied into O35 and P35, and then N35:P35 can be copied down the next five rows. This will give you your sorted table, and it will take account of duplicate percentages - your names should be unique, though. Hope this helps. Pete On Oct 25, 7:22 pm, Zombie0635 wrote: Here is my data table that is drawing data from a larger array of data. Win % Win Loss GB 78% Doug 7 2 -- 78% Justin 7 2 -- 67% Wade 6 3 1 63% Angela 5 3 1.5 57% Ben 4 3 2 50% Kelly 4 4 2.5 Note: Because I am not profficient with Excel I use multiple tables to break up my data into smaller pieces that I can handle until I finally get to my desired results. The above table is the table I am drawing from to get my final results. Here are the formulas I use on my final table: I use the "large function" to bring over the winning % in a descending order, than I use "vlookup's" to bring over the name, wins, and losses. Of course it doesnt matter if people have the same win/loss record because numbers can be identical, but it only returns one name for the same winning %. Here is an example of my "vlookup" formula for my name: =vlookup(l35,Sheet2!$G$8:$K$13,2,false). I hope this info sheds a little more light on the subject manner, and thanks for all of your help "Pete_UK" wrote: This will still get the first occurrence of 80%, not the subsequent ones. Pete On Oct 25, 7:49 am, claude jerry wrote: I think you are missing to enter a simple part in Vlookup =Vlookup(Cell To Search,Range to Search,Col No,0) E.g. =Vlookup(A1,$K1:$N1,2,0) Just try editing your existing formula by ending it with ,0) Just a guess I am taking as you have not posted the actual formula "Zombie0635" wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
same name being returned
Thanks for feeding back.
As I pointed out earlier, it basically adjusts the range of the table being looked at if the "score" is the same as the previous one - sorting by formula. Pete On Oct 26, 5:42 pm, Zombie0635 wrote: Thanks so much that worked out tremendously. I would have never figured it out. "Pete_UK" wrote: Still a bit short on detail, but here is what I think you have: In Sheet2 cells G8 to K13 you have a table of data like you have posted. This was a bit confusing as it already seems to be sorted, but I have assumed that is just coincidence, and sorted it by name in my test. In Sheet1 cells L35:L40 you have a formula like: =LARGE(Sheet2!G$8:G$13,ROW(A1)) copied down, which gives you the percentages from the previous table but in decreasing order. You can put this formula in M35 to get the first name, as you have already indicated: =VLOOKUP(L35,Sheet2!$G$8:$K$13,2,0) In M36, however, you need a formula like this: =IF(L36=L35,VLOOKUP(L36,INDIRECT("Sheet2!$G$"&8+MA TCH(M35,Sheet2!H$8:H $13,0)&":$K$13"),2,0),VLOOKUP(L36,Sheet2!$G$8:$K$1 3,2,0)) and then this formula should be copied down into M37:M40. You can then use this formula in N35: =VLOOKUP($M35,Sheet2!$H$8:$K$13,COLUMN(B1),0) and this can be copied into O35 and P35, and then N35:P35 can be copied down the next five rows. This will give you your sorted table, and it will take account of duplicate percentages - your names should be unique, though. Hope this helps. Pete On Oct 25, 7:22 pm, Zombie0635 wrote: Here is my data table that is drawing data from a larger array of data. Win % Win Loss GB 78% Doug 7 2 -- 78% Justin 7 2 -- 67% Wade 6 3 1 63% Angela 5 3 1.5 57% Ben 4 3 2 50% Kelly 4 4 2.5 Note: Because I am not profficient with Excel I use multiple tables to break up my data into smaller pieces that I can handle until I finally get to my desired results. The above table is the table I am drawing from to get my final results. Here are the formulas I use on my final table: I use the "large function" to bring over the winning % in a descending order, than I use "vlookup's" to bring over the name, wins, and losses. Of course it doesnt matter if people have the same win/loss record because numbers can be identical, but it only returns one name for the same winning %. Here is an example of my "vlookup" formula for my name: =vlookup(l35,Sheet2!$G$8:$K$13,2,false). I hope this info sheds a little more light on the subject manner, and thanks for all of your help "Pete_UK" wrote: This will still get the first occurrence of 80%, not the subsequent ones. Pete On Oct 25, 7:49 am, claude jerry wrote: I think you are missing to enter a simple part in Vlookup =Vlookup(Cell To Search,Range to Search,Col No,0) E.g. =Vlookup(A1,$K1:$N1,2,0) Just try editing your existing formula by ending it with ,0) Just a guess I am taking as you have not posted the actual formula "Zombie0635" wrote: I have set up a vlook function and I am returning peoples names for a given array of data, however if multiple people share the same data in this case percentage points it will return the same name over and over. I am not very good with "IF" statements but I am assuming that is what I will need to use to resolve this issue. i.e. I am looking up 80% and Doug and Justin share that score, so for the the two scores of 80% it is returning Doug twice instead of Doug once and Justin once. How do I get it to return one and then once that name is used than return the other one when that score appears again? Sorry for the question not being articulated very well. Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A value is returned | Excel Worksheet Functions | |||
#value returned instead of data | Excel Worksheet Functions | |||
Using the returned value of a function | New Users to Excel | |||
Result not being returned | Excel Worksheet Functions | |||
#Value! Returned ? | Excel Worksheet Functions |