Displaying a different value

[Mac]

Hi folks,

I'm running into some problems with a spreadsheet and was hoping
someone could point me in the right direction. What I need is a
spreadsheet with 3 columns for Employee #, First Name and Last name for
anywhere between 200 - 300 employees. From this list, I'm trying to
randomly select 5 Employee #'s, but have it display the first and last
name.

What I've tried already was making the IV column =RAND(), Column A is
Employee #, Column B is First Name and Column C is Last Name. Column E
has this formula in the first 5 cells:
=INDEX($A$2:$A$300,RANK($IV$2:$IV$300,$IV$2:$IV$300))

I'm running into 2 problems right now. First, the employee count can
fluctuate from 200 to 300 so some of the results could report back 0's
when it grabs a cell with no value. Secondly, it reports the Employee
# so you either have to scroll up and down until you match the # with
the name, or sort by the Employee # which refreshes the random results.

Is there a way to accomplish this, or am I hoping for too much doing
this through Excel?

[CLR]

Just put the =RAND() column next to the database and sort everything on it,
ascending or decending, then choose the top five rows ...........when the
sheet recaculates the RAND numbers will change, sort again and get five
different ones.........

hth
Vaya con Dios,
Chuck, CABGx3



"Mac" wrote:

> Hi folks,
>
> I'm running into some problems with a spreadsheet and was hoping
> someone could point me in the right direction. What I need is a
> spreadsheet with 3 columns for Employee #, First Name and Last name for
> anywhere between 200 - 300 employees. From this list, I'm trying to
> randomly select 5 Employee #'s, but have it display the first and last
> name.
>
> What I've tried already was making the IV column =RAND(), Column A is
> Employee #, Column B is First Name and Column C is Last Name. Column E
> has this formula in the first 5 cells:
> =INDEX($A$2:$A$300,RANK($IV$2:$IV$300,$IV$2:$IV$300))
>
> I'm running into 2 problems right now. First, the employee count can
> fluctuate from 200 to 300 so some of the results could report back 0's
> when it grabs a cell with no value. Secondly, it reports the Employee
> # so you either have to scroll up and down until you match the # with
> the name, or sort by the Employee # which refreshes the random results.
>
> Is there a way to accomplish this, or am I hoping for too much doing
> this through Excel?
>
>

[SteveG]

Mac,

You could try this.

In A2:A300 is your emp #, B2:B300 your First Names, C2:C300 your Last Names. In D,E & F is your random data. In D2,

=INDEX($A$2:$A$300,MATCH(RANDBETWEEN(MIN($A$2:$A$300),MAX($A$2:$A$300)),$A$2:$A$300))

In E2,

=VLOOKUP($D2,$A$1:$C$300,2,FALSE)

In F2,

=VLOOKUP($D2,$A$1:$C$300,3,FALSE)

Drag these down to row 6. The formula in column D will generate a random number between the smallest and largest emp #'s exclusively in your list of numbers. E & F return the first and last name of the random number generated from the formula in D. The only flaw with this is that sometimes E2:E6 may return the same value, in this case, you can just hit F9 to re-calculate until there are 5 unique entries. Using the amount of data you have, it generally only takes 1 or 2 re-calcs to get unique values. The other thing you may want to do is turn off the auto calculation so your values don't change everytime the sheet calcs.

HTH

Steve

[Mac]

Thanks for the reply Chuck. I was trying to keep it simple (i.e. hit
F9 and there's your list) mainly because it's going to a receptionist
who can't get too involved with the spreadsheet technically.

The more I play around with this, I don't think I can keep it to one
hot-key or click in Excel and achieve the desired results. I was
trying to avoid a macro route as well because this spreadsheet could
change hands between personnel and it could get lost, but that may be
the better option.

[Mac]

Thanks Steve! I'll give that a shot and let you know how it turns out.

[Mac]

I'm having a few problems with the formulas but I'm trying to fumble
through them. I'm not a whiz at Excel which doesn't help much.

D2 seems to have trouble with the
MATCH(RANDBETWEEN(MIN($A$2:$A$300),MAX($A$2:$A$300) part which I
haven't been able to solve yet. For E2 and F2 it was returning a
#NAME? so I changed $D2 to $D$2 and now it returns #N/A, which is
probably waiting for the D2 formula.

[Dave Peterson]

=Randbetween() is part of the analysis toolpak.

Tools|addins

(Depending on how you installed excel, you may need the installation CD)

Mac wrote:
>
> I'm having a few problems with the formulas but I'm trying to fumble
> through them. I'm not a whiz at Excel which doesn't help much.
>
> D2 seems to have trouble with the
> MATCH(RANDBETWEEN(MIN($A$2:$A$300),MAX($A$2:$A$300) part which I
> haven't been able to solve yet. For E2 and F2 it was returning a
> #NAME? so I changed $D2 to $D$2 and now it returns #N/A, which is
> probably waiting for the D2 formula.

--

Dave Peterson

[Mac]

That did the trick Dave, thanks for your input. Everything is up and
running, and I structured the forumlas for D2 - D6 to query different
blocks of employee numbers to avoid duplicates.

I really appreciate all the help. Thanks to everyone.

[CLR]

Macros are generally my solution of choice for most anything I, (or my users)
have to do frequently.

Vaya con Dios,
Chuck, CABGx3



"Mac" wrote:

> Thanks for the reply Chuck. I was trying to keep it simple (i.e. hit
> F9 and there's your list) mainly because it's going to a receptionist
> who can't get too involved with the spreadsheet technically.
>
> The more I play around with this, I don't think I can keep it to one
> hot-key or click in Excel and achieve the desired results. I was
> trying to avoid a macro route as well because this spreadsheet could
> change hands between personnel and it could get lost, but that may be
> the better option.
>
>