Random select one line of data for each person in a list

[rory1111]

I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but i would like to try it on a large set of data. any ideas of method would be greatful, i have been able to get a random transaction but not one for each person.


REF_ID NAME DATE BILL TOTAL

BA2234340 Bob 10/12/2012 € 22.00

BA7377373 Jenny 11/12/2012 € 45.00

BA6326363 Paul 19/12/2012 € 29.00

BA6366363 Mary 14/12/2012 € 51.00

BA6363663 Max 11/12/2012 € 21.00

BA4545394 Pat 14/12/2012 € 28.00

BA0028737 Neil 11/12/2012 € 37.00

BA7326722 Katy 14/12/2012 € 33.00

BA1111123 Trish 10/12/2012 € 12.00

BA7324734 Max 14/12/2012 € 124.00

BA7324732 Trish 10/12/2012 € 33.00

BA8934832 Max 11/12/2012 € 44.00

BA2345566 Trish 14/12/2012 € 55.00

BA0000333 Bob 18/12/2012 € 57.00

BA9999999 Bob 14/12/2012 € 8.00

BA7777777 Bob 01/12/2012 € 97.00

BA4432444 Benny 11/12/2012 € 87.00


Jenny

Paul

Mary

Max

Pat

Neil

Katy

Trish

Benny

 

[rory1111]

sorry format went a little crazy, it should read

REF_ID -- NAME -- DATE -- BILL -- TOTAL

 

[keymaster]

@Rory1111... Welcome to Chandoo.org forums and thanks for your question. Please take a minute to explore our forums and see what is already available.

You can start by reading the sticky posts on the forum main page - http://chandoo.org/forums/


Coming to your question:


You can do this using INDEX, MATCH, COUNTIF and RANDBETWEEN formulas. The idea is like this:


1) First add an extra column to your data where we list each name followed by its occurrence number. So first bob would be bob1, second bob would be bob2, first jenny would be jenny1 etc.

2) Then we use MATCH to find a random bob out of all bobs. That is MATCH("bob"&randbetween(1,countofbobs), extra_column_range, 0)

3) Finally we use this MATCH output in INDEX to fetch corresponding row details.


See this attached example file to understand how to do this.


http://img.chandoo.org/playground/random-selection-by-criteria.xlsx

 

[rory1111]

Chandoo thank you so much for your response. I have been analysing you suggestions and trying to make sense of it.


I think I understand the below "B11&COUNTIF($B$2:B11,B11") so you want to place b11 and the number of times b11 occurs from b2:b11


I am having difficulty understanding the mechanics of the following

"=INDEX($A$2:$A$18,MATCH(H4&RANDBETWEEN(1,I4),$E$2:$E$18,0))"


From my research I have found that =index will return values from rows/cells

Match will search for a cell location, used together you can search for a value and return a result.


"=INDEX($A$2:$A$18,MATCH(H4&RANDBETWEEN(1,I4),$E$2:$E$18,0))"

Is this saying "=INDEX($A$2:$A$18" display a result from a2:a18, "MATCH(H4&RANDBETWEEN(1,I4)" find location of cell/row/column (h4 and a random number between 1 and i4) ,"$E$2:$E$18,0))" from cell e2:e18 otherwise 0


Additionally the results in cells k2,k3 return a random search that is from a different row than random result in j2. I have been able to use vlookup to find the corresponding info eg. In cell m2 "=VLOOKUP(J2,$A$2:$E$18,3,FALSE)". This can be reused to retrieve corresponding data from other columns, however my question is …….can this be done a smarter way as adding a multiple vlookups to find NAME, DATE, TOTAL works but is probably not considered good practice.