Random Selection in Excel With Conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-3434886%22%20slang%3D%22en-US%22%3ERandom%20Selection%20in%20Excel%20With%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3434886%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20task%20required%20in%20Excel%20that%20I%20need%20to%20do%20monthly%20and%20I%20know%20someone%20smarter%20than%20me%20has%20a%20better%20way%20of%20getting%20it%20done.%26nbsp%3B%20Any%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20pull%20a%20defined%20set%20of%20random%20unique%20ID%20numbers%20after%20meeting%20a%20few%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20below%20is%20small%20and%20completely%20randomized.%26nbsp%3B%20My%20real%20tables%20are%20more%20than%20180%2C000%20rows%20so%20imagination%20is%20important%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReport%2010%20random%20ID%23%20for%20each%20STATE%20that%20equal%20both%20Red%20%26amp%3B%20Sprockets.%26nbsp%3B%20Also%2C%20separately%2C%20report%205%20random%20ID%23%20for%20each%20STATE%20that%20equal%20both%20Blue%20%26amp%3B%20Cogs.%3C%2FP%3E%3CTABLE%20width%3D%22266%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EState%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EIndustry%3C%2FTD%3E%3CTD%20width%3D%2274%22%3ERed%20or%20Blue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3ECA%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3EGreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EAZ%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3EBlue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3ECT%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3EAR%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3EBlue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3ECO%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3EGreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3EAK%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EFL%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3EGreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3ECT%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3EBlue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3ECA%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3EDE%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3ECO%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3EGreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EGA%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3EBlue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3EAK%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3EGA%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3EBlue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3EAZ%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3EGreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3EDE%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3EGreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%3C%2FTD%3E%3CTD%3EFL%3C%2FTD%3E%3CTD%3ECogs%3C%2FTD%3E%3CTD%3EBlue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%3C%2FTD%3E%3CTD%3EAR%3C%2FTD%3E%3CTD%3ESprockets%3C%2FTD%3E%3CTD%3ERed%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%2C%20the%20way%20I%20have%20to%20do%20this%20is%20filter%20out%20and%20remove%20all%20rows%20where%20the%20Color%20does%20not%20%3DRed%20or%20Blue.%3C%2FP%3E%3CP%3EThen%20I%20use%20%3DRAND%20to%20assign%20a%20random%20number%20to%20any%20column%20and%20copy%20paste%20the%20values.%26nbsp%3B%20I%20use%20multiple%20sort%20to%20sort%20the%20table%20first%20by%20Random%20Value%20and%20State%20and%20Industry.%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20I%20filter%20my%20list%20by%20Cogs%20and%20Red%20and%20then%20for%20each%20State%20I%20select%20the%20first%2010%20IDs%2C%20switching%20the%20State%20filter%20each%20time.%3C%2FP%3E%3CP%3ERepeat%20those%20steps%20again%20for%20Blue%20Cogs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20has%20to%20be%20a%20way%20for%20me%20to%20say%2C%20%22Report%20the%20ID%20number%20of%20ten%20random%20Red%20Sprockets%20and%20five%20random%20Blue%20Cogs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20y'all.%26nbsp%3B%20Curious%20to%20see%20what%20someone%20can%20come%20up%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETravis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3434886%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3438946%22%20slang%3D%22en-US%22%3ERe%3A%20Random%20Selection%20in%20Excel%20With%20Conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1403432%22%20target%3D%22_blank%22%3E%40HeyBaby_QuePaso%3C%2FA%3E%26nbsp%3B%20Here%20is%20an%20example%20of%201%20option.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mtarler_0-1653665274004.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22mtarler_0-1653665274004.png%22%20style%3D%22width%3A%20370px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F375780i933BAEA35C227B0D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mtarler_0-1653665274004.png%22%20alt%3D%22mtarler_0-1653665274004.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ein%20the%20attached%20I%20create%20a%20N%20randomized%20filtered%20list%20of%20the%20Table%20based%20on%20the%203%20criteria%20(state%2C%20industry%2C%20color).%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

IDState IndustryRed or Blue
1CASprocketsGreen
2AZCogsBlue
3CTSprocketsRed
4ARSprocketsBlue
5COSprocketsGreen
6AKCogsRed
7FLCogsGreen
8CTSprocketsBlue
9CASprocketsRed
10DECogsRed
11COCogsGreen
12GACogsBlue
13AKCogsRed
14GASprocketsBlue
15AZSprocketsGreen
16DECogsGreen
17FLCogsBlue
18ARSprocketsRed

 

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

1 Reply

@HeyBaby_QuePaso  Here is an example of 1 option.  

mtarler_0-1653665274004.png

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