Random Selection in Excel With Conditions

[HeyBaby_QuePaso]

Hello All,

I have a task required in Excel that I need to do monthly and I know someone smarter than me has a better way of getting it done.  Any help?

I need to pull a defined set of random unique ID numbers after meeting a few criteria.

The table below is small and completely randomized.  My real tables are more than 180,000 rows so imagination is important here.

Report 10 random ID# for each STATE that equal both Red & Sprockets.  Also, separately, report 5 random ID# for each STATE that equal both Blue & Cogs.

ID	State	Industry	Red or Blue
1	CA	Sprockets	Green
2	AZ	Cogs	Blue
3	CT	Sprockets	Red
4	AR	Sprockets	Blue
5	CO	Sprockets	Green
6	AK	Cogs	Red
7	FL	Cogs	Green
8	CT	Sprockets	Blue
9	CA	Sprockets	Red
10	DE	Cogs	Red
11	CO	Cogs	Green
12	GA	Cogs	Blue
13	AK	Cogs	Red
14	GA	Sprockets	Blue
15	AZ	Sprockets	Green
16	DE	Cogs	Green
17	FL	Cogs	Blue
18	AR	Sprockets	Red

Currently, the way I have to do this is filter out and remove all rows where the Color does not =Red or Blue.

Then I use =RAND to assign a random number to any column and copy paste the values.  I use multiple sort to sort the table first by Random Value and State and Industry. 

Then, I filter my list by Cogs and Red and then for each State I select the first 10 IDs, switching the State filter each time.

Repeat those steps again for Blue Cogs.

Ideas?

There has to be a way for me to say, "Report the ID number of ten random Red Sprockets and five random Blue Cogs.

Thanks y'all.  Curious to see what someone can come up with.

Travis

[#2]

@HeyBaby_QuePaso  Here is an example of 1 option.  

in the attached I create a N randomized filtered list of the Table based on the 3 criteria (state, industry, color). see attached.