random of 4 names

D

duckie

Have cells B4 to B84 with names in them in cell C4 to C84 have 4
differance titles in it, in cell E I need one of each title in blocks
of 4
at random
 
J

James Ravenswood

The first step is to sort the data by title, Here is typical B4 thru
C84, sorted:

Michael Baron
William Baron
David Baron
Richard Baron
Charles Baron
Joseph Baron
Thomas Baron
Christopher Baron
Daniel Baron
Paul Baron
Mark Baron
Donald Baron
George Baron
Kenneth Baron
Steven Baron
Edward Baron
Brian Baron
Ronald Baron
Anthony Baron
Kevin Baron
Jason Baron
Matthew Duke
Gary Duke
Timothy Duke
Jose Duke
Larry Duke
Jeffrey Duke
Frank Duke
Scott Duke
Eric Duke
Stephen Duke
Andrew Duke
Raymond Duke
Gregory Duke
Joshua Duke
Jerry Duke
Dennis Duke
Walter Duke
Patrick Duke
Peter Duke
Harold Duke
Douglas Earl
Henry Earl
Carl Earl
Arthur Earl
Ryan Earl
Roger Earl
Joe Earl
Juan Earl
Jack Earl
Albert Earl
Jonathan Earl
Justin Earl
Terry Earl
Gerald Earl
Keith Earl
Samuel Earl
Willie Earl
Ralph Earl
Lawrence Earl
Nicholas Earl
Roy Count
Benjamin Count
Bruce Count
Brandon Count
Adam Count
Harry Count
Fred Count
Wayne Count
Billy Count
Steve Count
Louis Count
Jeremy Count
Aaron Count
Randy Count
Howard Count
Eugene Count
Carlos Count
Russell Count
Bobby Count
Victor Count

Clearly the Barons are in rows 4 thru 24. The Dukes are in rows 25
thru 44. The Earls are in rows 45 thru 64. The Counts are in rows 65
thru 84.

To pick a Baron,Duke, Earl, and Count at random, use these formulae:

=OFFSET(B1,RANDBETWEEN(4,24)-1,0)
=OFFSET(B1,RANDBETWEEN(25,44)-1,0)
=OFFSET(B1,RANDBETWEEN(45,64)-1,0)
=OFFSET(B1,RANDBETWEEN(65,84)-1,0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top