Displaying a different value

  • Thread starter Thread starter Mac
  • Start date Start date
M

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?
 
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,

You could try this.

In A2:A300 is your emp #, B2:B300 your First Names, C2:C300 your Las
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
random number between the smallest and largest emp #'s exclusively i
your list of numbers. E & F return the first and last name of th
random number generated from the formula in D. The only flaw with thi
is that sometimes E2:E6 may return the same value, in this case, you ca
just hit F9 to re-calculate until there are 5 unique entries. Using th
amount of data you have, it generally only takes 1 or 2 re-calcs to ge
unique values. The other thing you may want to do is turn off the aut
calculation so your values don't change everytime the sheet calcs.

HTH

Stev
 
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.
 
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.
 
=Randbetween() is part of the analysis toolpak.

Tools|addins

(Depending on how you installed excel, you may need the installation CD)
 
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.
 
Macros are generally my solution of choice for most anything I, (or my users)
have to do frequently.

Vaya con Dios,
Chuck, CABGx3
 
Back
Top