Random generator lookup formula

[erniepoe]

Hi all,

So I have a list of program names in column A (many of these programs are listed dozens of times), and I want to randomly choose 7 unique programs at a time, which i do with the below formula pasted into 7 cells in the table I've created.

=INDEX($A:$A,RANDBETWEEN(2,COUNTA($A:$A)),1)


Now, once I've randomly selected a program, I want it to randomly select the "application received date," which it the data in column B. Most programs have many application received dates, and i am hoping to choose three random such dates for each of the random programs chosen from column A. I can't just use the above formula because I need it to be random but be one of the application dates from the random program chosen from column A.


Further, once I've chosen a random application date from the random program, I just want to put in the contact name from that application, which are listed in column C (last name) and column D (first name). So I don't want the names to be random, as a specific name goes along with each application. So once it chooses a random cell in column B for the application date, I just want to grab the corresponding data in columns C and D and paste it in.

Does this make any sense to anybody? Any help is much obliged.

 

[erniepoe]

any ideas?

 

[shg]

How does your existing formula manage to choose seven unique programs? It could generate duplicates, even if each program is appears only once.

If that somehow happens do what you want, then just use a helper column with the formula =RAND(), sort the whole magilla by that column to put everything in random order, then choose the top 7 lines.

 

[moonfish]

I tried it without using VBA. I suppose it was a bit of an exercise. Ended up being rather elaborate:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	chosen	product2	product3	product8	product6	product3	product5	product1
2	*	*	*	count	6	14	12	16	14	11	8
3	*	*	*	random	3	3	8	4	10	1	6
4	*	*	*	*	4	2	1	16	7	2	1
5	*	*	*	*	5	10	12	3	2	4	2
6	*	*	*	dates	10/14/2009	2/14/2010	3/19/2010	2/27/2012	1/23/2010	1/12/2010	10/10/2009
7	*	*	*	*	8/18/2012	2/13/2011	2/20/2012	7/14/2011	7/21/2010	2/27/2012	12/11/2010
8	*	*	*	*	5/10/2009	1/23/2010	3/23/2009	1/19/2009	2/13/2011	6/12/2011	7/10/2011
9	*	*	*	names	name5	name3	name5	name5	name3	name6	name7
10	*	*	*	*	name5	name3	name8	name5	name6	name9	name3
11	*	*	*	*	name3	name3	name7	name7	name3	name4	name9
12	*	*	*	surnames	surname5	surname1	surname2	surname3	surname8	surname7	surname7
13	*	*	*	*	surname8	surname4	surname4	surname4	surname7	surname6	surname6
14	product	date	name	surname	surname7	surname8	surname8	surname7	surname4	surname8	surname7
15	product5	7/18/2012	name6	surname7	*	*	*	*	*	1	*
16	product9	8/22/2009	name3	surname1	*	*	*	*	*	*	*
17	product1	5/14/2010	name3	surname6	*	*	*	*	*	*	1
Sheet

Each column represents a chosen product.

The formulas in E3, E6, E9 and E12 have to be copied across the areas with the same colors.
The formula in E15 has to be copied across the row.
The formula in E16 has to be copied across the row, and then across each row that contains a product.

It should be noted that this does not choose unique values, as shg pointed out. You can adjust the way the random values in E3:K5 are generated to change this.

 

[erniepoe]

Thanks for the suggestions guys, much appreciated.

So, just for my own edification, would there be a formula or macro that would allow me to pick 7 random and unique names from a column of names?

 

[shg]

Did you try this?

just use a helper column with the formula =RAND(), sort the whole magilla by that column to put everything in random order, then choose the top 7 lines.

 

[erniepoe]

I did try that, SHG, and thanks. However, I always end up running into the "run time error 91" debugging problem if I record that as a macro and want to refresh multiple times, so it would be nice if there were a random selection formula that could find several unique values.


I guess I can work around it, but was hopeful for a better solution. Cheers!

 

[shg]

If you put a workbook on box.net with an example of your data layout and expected result, and then post a link here, I'll look at it.

 

[moonfish]

Out of curiosity, did the excel functions I wrote generate the desired results?
If so, you could apply the same logic with the use of vba.

 

[erniepoe]

Thanks moonfish, but your formulas didn't really work. At least, I couldn't get them to work, they just resulted in a lot of #NUM errors. I wasn't quite sure why your formulas kept referring to E5:E113; what's supposed to be there? What do all your asterisks represent?