Randomize a choice from a sub-section?

[fishoutofwater]

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

 

[Just Jan]

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.

 

[Yahya]

try this

Excel Workbook
	A	B	C	D
1	Fred	I like ice cream	Fred	I like ice cream
2	Fred	I like baseball
3	Fred	I like the color red
4	Sally	I go hiking on Monday
5	Sally	I go running on Tuesday
6	Joe	Hello
7	Joe	Greetings
8	Joe	Howdy
9	Joe	What's Up
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.