Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
I am attempting to create a Random Drug Screen Identifier. I can handle the
whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
Add ,False to the end as so:
=VLOOKUP(D1,A1:A150,2,False) -- -John Please rate when your question is answered to help us and others know what is helpful. "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
Look in help under VLOOKUP parameters. It has 3 required and 1 optional
argument. VLOOKUP(lookup_value,table,column,TRUE/FALSE) lookup_value I think is fine, but you define your table as A1:A150, and then are trying to pull the second column of the table, which doesn't exist. You need to expand your table reference For example: =VLOOKUP(D1,$A$1:$C$150,2,FALSE)&", "&VLOOKUP(D1,$A$1:$C$150,3,FALSE) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
Use
=VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
Just noticed you will need to make it
=VLOOKUP(D1,A1:B150,2,False) forgot the b -- -John Please rate when your question is answered to help us and others know what is helpful. "John Bundy" wrote: Add ,False to the end as so: =VLOOKUP(D1,A1:A150,2,False) -- -John Please rate when your question is answered to help us and others know what is helpful. "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Thanks...however...
Thanks for your help. And now for a new challenge...
Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
That always returns a value of #N/A
"John Bundy" wrote: Just noticed you will need to make it =VLOOKUP(D1,A1:B150,2,False) forgot the b -- -John Please rate when your question is answered to help us and others know what is helpful. "John Bundy" wrote: Add ,False to the end as so: =VLOOKUP(D1,A1:A150,2,False) -- -John Please rate when your question is answered to help us and others know what is helpful. "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
Hi,
Such a time! Since the number in the first column are in order 1, A1+1,... the VLOOKUP is not failing because of the last argurment. For exact matches the sort order is not important, for approximate matches the sort must by ascending on the lookup column, but your is. So the most likely problem is that the random numbers are not numbers, they are text or the numbers in column A are text, less likely. This problem and its solutions are discussed below: Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. -- Thanks, Shane Devenshire "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Thanks...however...
My formula given should give you last name, first name. If you are getting
the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
Using the ISNUMBER function, both the first column and the random numbers return a value of TRUE. Thanks for taking the time to pen such a detailed explanation. Do you know of any other possible fixes? Thanks again. "ShaneDevenshire" wrote: Hi, Such a time! Since the number in the first column are in order 1, A1+1,... the VLOOKUP is not failing because of the last argurment. For exact matches the sort order is not important, for approximate matches the sort must by ascending on the lookup column, but your is. So the most likely problem is that the random numbers are not numbers, they are text or the numbers in column A are text, less likely. This problem and its solutions are discussed below: Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. -- Thanks, Shane Devenshire "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number generation
=RND()*($F$15)+A$1
F15 is the total number of employees F16 is the number of open positions F15 is determined by subtracting the number of open positions (F16) from the total number of positions (150) F15 = 150-F16 F16 = COUNTIF(B1:B150,"") "John C" wrote: My formula given should give you last name, first name. If you are getting the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number generation
I assume that you meant RAND(), not RND() ?
-- David Biddulph "REJesser" wrote in message ... =RND()*($F$15)+A$1 F15 is the total number of employees F16 is the number of open positions F15 is determined by subtracting the number of open positions (F16) from the total number of positions (150) F15 = 150-F16 F16 = COUNTIF(B1:B150,"") "John C" wrote: My formula given should give you last name, first name. If you are getting the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random number generation
You are correct. It is RAND().
"David Biddulph" wrote: I assume that you meant RAND(), not RND() ? -- David Biddulph "REJesser" wrote in message ... =RND()*($F$15)+A$1 F15 is the total number of employees F16 is the number of open positions F15 is determined by subtracting the number of open positions (F16) from the total number of positions (150) F15 = 150-F16 F16 = COUNTIF(B1:B150,"") "John C" wrote: My formula given should give you last name, first name. If you are getting the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Thanks...however...
Perhaps I didn't do a good job of explaining the new opportunity facing me.
The same person is not always returned. As long as the last names the random numbers correspond to are unique, everything works perfect. If, however, there are two or more folks with the same last name, the first person (alphabetically by first name) with that last name is selected. So... 1. Britt, Justin 2. Britt, Wesley 3. Bryant, Bear 4. Namath, Joe 5. Saban, Nick 6. Stabler, Ken 7. Starr, Bart 8. Thomas, Derrick Suppose that the two random numbers generated are 4 and 2. The names that would be listed a Namath, Joe Britt, Justin (should have been his brother Britt, Wesley) "John C" wrote: My formula given should give you last name, first name. If you are getting the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Thanks...however...
Why bother using Vlookup at all? If you are generating a random number,
simply use that as the row number for the name you want. Regards, Fred. "REJesser" wrote in message ... Perhaps I didn't do a good job of explaining the new opportunity facing me. The same person is not always returned. As long as the last names the random numbers correspond to are unique, everything works perfect. If, however, there are two or more folks with the same last name, the first person (alphabetically by first name) with that last name is selected. So... 1. Britt, Justin 2. Britt, Wesley 3. Bryant, Bear 4. Namath, Joe 5. Saban, Nick 6. Stabler, Ken 7. Starr, Bart 8. Thomas, Derrick Suppose that the two random numbers generated are 4 and 2. The names that would be listed a Namath, Joe Britt, Justin (should have been his brother Britt, Wesley) "John C" wrote: My formula given should give you last name, first name. If you are getting the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Thanks...however...
You will have to excuse my ignorance. How would I go about doing that?
"Fred Smith" wrote: Why bother using Vlookup at all? If you are generating a random number, simply use that as the row number for the name you want. Regards, Fred. "REJesser" wrote in message ... Perhaps I didn't do a good job of explaining the new opportunity facing me. The same person is not always returned. As long as the last names the random numbers correspond to are unique, everything works perfect. If, however, there are two or more folks with the same last name, the first person (alphabetically by first name) with that last name is selected. So... 1. Britt, Justin 2. Britt, Wesley 3. Bryant, Bear 4. Namath, Joe 5. Saban, Nick 6. Stabler, Ken 7. Starr, Bart 8. Thomas, Derrick Suppose that the two random numbers generated are 4 and 2. The names that would be listed a Namath, Joe Britt, Justin (should have been his brother Britt, Wesley) "John C" wrote: My formula given should give you last name, first name. If you are getting the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP matches
I am not sure whether you got your answer or not...
In case you did not then use this in E1 to get the first name for the number in D1 =INDEX(A:C,D1,3) If you want the last name too, then enter this in F1 =INDEX(A:B,D1,2) |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Thanks...however...
Use the Index function, as in:
=index(A2:A47,<your random number formula,1) Adjust the range to suit your situation. Regards, Fred. "REJesser" wrote in message ... You will have to excuse my ignorance. How would I go about doing that? "Fred Smith" wrote: Why bother using Vlookup at all? If you are generating a random number, simply use that as the row number for the name you want. Regards, Fred. "REJesser" wrote in message ... Perhaps I didn't do a good job of explaining the new opportunity facing me. The same person is not always returned. As long as the last names the random numbers correspond to are unique, everything works perfect. If, however, there are two or more folks with the same last name, the first person (alphabetically by first name) with that last name is selected. So... 1. Britt, Justin 2. Britt, Wesley 3. Bryant, Bear 4. Namath, Joe 5. Saban, Nick 6. Stabler, Ken 7. Starr, Bart 8. Thomas, Derrick Suppose that the two random numbers generated are 4 and 2. The names that would be listed a Namath, Joe Britt, Justin (should have been his brother Britt, Wesley) "John C" wrote: My formula given should give you last name, first name. If you are getting the same person every time, perhaps you should share with us how you are generating the random number? -- ** John C ** "REJesser" wrote: Thanks for your help. And now for a new challenge... Your suggestion worked out great except when more than one individual has the same last name. When that takes place, the first person with that last name is identified. Ex. 1. Smith, Alvin 2. Smith, Brian 3. Smith, Cory Even when the #3 is randomly generated, Alvin Smith is still identified. "Sheeloo" wrote: Use =VLOOKUP(D1,A1:B150,2). i.e. change A150 to B150 "REJesser" wrote: I am attempting to create a Random Drug Screen Identifier. I can handle the whole random number creation. What I haven't been able to figure out is how can I have the name associated with the number identified. For example, I have in the first column, the number 1. Each cell below counts up...=A1+1; =A2+1, etc. The next column (B) is devoted to the last names of the employees. The next is employee first names (C). Column D generates 10 random numbers from 1 to the total current number of employees. I can certainly print out the list of employees and, by hand, highlight those numbers which were generated. I would prefer the worksheet do that for me. I have tried the VLOOKUP command... =VLOOKUP(D1,A1:A150,2). I had hoped that this would actually list the name identified with the first random number. The next number would be =VLOOKUP(D2,A1:A150,2) and so on. It ain't workin'. Any help would be greatly appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup not finding matches | Excel Worksheet Functions | |||
VLOOKUP and multiple matches | Excel Discussion (Misc queries) | |||
Approximate matches with vlookup? | Excel Worksheet Functions | |||
Vlookup 2 data matches? | Excel Discussion (Misc queries) | |||
Multiple matches on VLOOKUP | Excel Worksheet Functions |