Random Selection Based on Criteria

[FisheriesTech]

I have a tricky question, which may be too complex/too much work for someone to bother answering, but I'll try anyways.
I'll use the below simple data set to try and illustrate my question.

In column A I have a unique Identifier and in Column B I have values that are associated with that Identifier. See below

What I want to do is pick one random value from column B for each unique value in column A and paste it into a separate column. For example there are 8 Alpha values in column A and each have an associated value in column B. I would like one of the column B values to be randomly selected for each unique value in column A and copied over to another column. I then need to repeat it for the other two Unique Values in column A. I currently have a VBA script written which uses an If statement to identify a unique value (for example the value 'Alpha') in column A and if it meets the criteria it copies the associated value from column B to another column. It then repeats the process for all of column A, populating a column of eight values (because there are eight cells in column A that contain the value 'Alpha'). The script then chooses a random value from there and copies it over to another column. This is great but I then have to write a whole new script to repeat the process for value Bravo and then for Delta and repeat the process. The catch is that in my real data set I have several hundred unique identifiers in column A. SO I would need to write several hundred scripts. Is there a way to ask Excel to go through column A, store each unique value as a 'Key' (without me having to identify each unique value) and then for each unique Key have Excel choose a random value associated with that key (would be one of the values from column B) and populate another column with these values. So if say I have a hundred unique Identifiers then I would end up with a hundred values from cell B associated with each of those unique identifiers but picked at random. The result would look something like below:

*Yellow cells *resulting random values
picked at random
for each identifier in
column A

I looked at using VBA dictionary but can't wrap my head around it. Maybe I'm asking for too much.
Any help appreciated.

Thank you kickass people out there!

 

[Eric W]

Try this:

Book1
	A	B	C	D	E	F
1	Key	Value			Key	Value
2	Alpha	2			Alpha	2
3	Bravo	1			Bravo	8
4	Delta	4			Delta	4
5	Alpha	2
6	Bravo	1
7	Delta	0
8	Alpha	9
9	Bravo	4
10	Delta	7
11	Alpha	0
12	Bravo	2
13	Delta	4
14	Alpha	10
15	Bravo	9
16	Delta	8
17	Alpha	5
18	Bravo	6
19	Delta	2
20	Alpha	3
21	Bravo	8
22	Delta	7
23	Alpha	4
24	Bravo	2
25	Delta	1
Sheet10

Cell Formulas
Range	Formula
E2:E6	E2	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$25)/(COUNTIF($E$1:$E1,$A$2:$A$25)=0),1)),"")
F2:F6	F2	=IF(E2="","",AGGREGATE(15,6,$B$2:$B$25/($A$2:$A$25=E2),RANDBETWEEN(1,COUNTIF($A$2:$A$25,E2))))

 

[FisheriesTech]

Try this:

Book1
	A	B	C	D	E	F
1	Key	Value			Key	Value
2	Alpha	2			Alpha	2
3	Bravo	1			Bravo	8
4	Delta	4			Delta	4
5	Alpha	2
6	Bravo	1
7	Delta	0
8	Alpha	9
9	Bravo	4
10	Delta	7
11	Alpha	0
12	Bravo	2
13	Delta	4
14	Alpha	10
15	Bravo	9
16	Delta	8
17	Alpha	5
18	Bravo	6
19	Delta	2
20	Alpha	3
21	Bravo	8
22	Delta	7
23	Alpha	4
24	Bravo	2
25	Delta	1
Sheet10

Cell Formulas
Range	Formula
E2:E6	E2	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$25)/(COUNTIF($E$1:$E1,$A$2:$A$25)=0),1)),"")
F2:F6	F2	=IF(E2="","",AGGREGATE(15,6,$B$2:$B$25/($A$2:$A$25=E2),RANDBETWEEN(1,COUNTIF($A$2:$A$25,E2))))

Oh my gosh!!! How?? It worked perfect. I have to hit the books to see what you did there. Such an elegant answer to the issue. Thank you so much. I didn't think it would be possible to be honest.

 

[Eric W]

Excel has lots of tricks like that! Nobody knows them all. Glad I could help, and thanks for the feedback! ?