May 26 2022 10:35 AM
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
May 27 2022 08:30 AM
@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.