I am doing a pool for the show The Bachelor. I need to assign each of my "people" with a "contestant" at random.
I have a sheet with 2 columns of data.
Column A has 28 people in rows 2:29
Column H has 28 contestants in rows 2:29
What formula can I put in Column C to randomly assign a "contestant" from H with no duplicates?
Try this array formula in C2, copied down:
=INDEX($H$2:$H$29, LARGE(MATCH(ROW($H$2:$H$29), ROW($H$2:$H$29))*NOT(COUNTIF($C$1:C1, $H$2:$A$29)), RANDBETWEEN(1, ROWS($H$2:$H$29)-ROW(A1)+1)))
Confirm the formula by clicking CTRL+SHIFT+ENTER.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
One way...
Data Range
Enter this formula in E2 and copy down as needed.
=RAND()
Enter this formula in B2 and copy down as needed.
=INDEX(D$2:D$11,MATCH(SMALL(E$2:E$11,ROWS(B$2:B2)),E$2:E$11,0))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank You!
You are most welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks