Randomize a choice from a sub-section?

F

fishoutofwater

New Member
Joined
Jul 20, 2011
Messages
38
In column A I have a list of names, but each name is associate with 5 to 20 pieces of data, column b has the data. Like this:

A..........B
Fred.....I like ice cream
Fred.....I like baseball
Fred.....I like the color red
Sally.....I go hiking on Monday
Sally.....I go running on Tuesday
Joe.......Hello
Joe.......Greetings
Joe.......Howdy
Joe.......What's Up

Imagine in C1 I have: "Fred" in D1 I want any single one of the things Fred said, but a random selection. So each time I hit f9 I get a new random choice-- but only from the things Fred says!

Then, if I change C1 to "Sally" I want D1 to give me one of the random things from the Sally lines in column B (ie, B4 or B5) and then if I change C1 to "Joe" I want one of the Joe-B-column comments, but again-- a random one.

So what do I put in D1 to get this? I have been playing with weird mashups of Index, Vloolup, and Rand, but I am not even close :( to it working
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
A & B as per your question
C2 =IF(A2=$D$1,COUNTIF(A$2:A2,A2),"") copy down list
D1 is name to search on
E1 =INDEX(B:B,MATCH(RANDBETWEEN(1,MAX(C:C)),C:C,0))

Does not require sorting into groups by name but you could sort if you wanted to see the grouped data all together.

You might want to turn off Autocalculate if you need to use the result, otherwise it will keep changing.
 
Last edited:
Upvote 0
try this
Excel Workbook
ABCD
1FredI like ice creamFredI like ice cream
2FredI like baseball
3FredI like the color red
4SallyI go hiking on Monday
5SallyI go running on Tuesday
6JoeHello
7JoeGreetings
8JoeHowdy
9JoeWhat's Up
Sheet1
Excel 2007
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
293
shg
shg
N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
724
Peter_SSs
Peter_SSs
M
  • Solved
Data Validation list from multiple columns.
Replies
1
Views
401
rlv01
R
A
  • Solved
Automate data export from multiple word tables into excel
Replies
8
Views
158
NdNoviceHlp
NdNoviceHlp
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
959
MoonDancer
M

Forum statistics

Threads
1,224,548
Messages
6,179,445
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top