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.

 

[moonfish]

I don't particularly like that I spent quite some time solving your problem and you didn't even read my post. I specified these formulas:

E15 =IF(E$1=$A15,1,"")
E16 =IF(E$1=$A16,MAX(E$15:E15)+1,"")

Also, I said this:

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.