Home

Randomly select rows with criteria-based requirement across multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-661707%22%20slang%3D%22en-US%22%3ERandomly%20select%20rows%20with%20criteria-based%20requirement%20across%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-661707%22%20slang%3D%22en-US%22%3E%3CP%3ESee%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20auto-assign%20some%20values%20based%20on%20criteria.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEither%20select%20PV%20or%20TC%20or%20LB%20with%20certain%20requirements%3C%2FP%3E%3CUL%3E%3CLI%3EHave%20at%20least%2014%20columns%20with%20reps%3C%2FLI%3E%3CLI%3EAssign%20a%20certain%20number%20of%20PV%20or%20TC%20at%20certain%20times%20of%20the%20day.%20The%20overflow%20would%20be%20auto-assigned%20to%20TC%20or%20LB%3C%2FLI%3E%3CLI%3ELB%20is%20only%20assigned%20at%20certain%20times%3C%2FLI%3E%3CLI%3EIF%20LB%20equals%200%2C%20no%20need%20to%20assign%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI've%20tried%20to%20use%20%3DChoose%20or%20%3DRAND%20but%20to%20no%20avail.%20Is%20this%20even%20possible%20to%20complete%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-661707%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675884%22%20slang%3D%22en-US%22%3ERe%3A%20Randomly%20select%20rows%20with%20criteria-based%20requirement%20across%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675884%22%20slang%3D%22en-US%22%3E%3CP%3EUPDATE%3A%20So%20I%20have%20this%20thus%20far%3A%20%3DCHOOSE(RANDBETWEEN(1%2C2)%2C%20%22PV%22%2C%20%22TC%22)%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20could%20choose%20PV%20or%20TC%20based%20on%20a%20criteria%20e.g.%20only%20assign%204%20PV%20but%20randomly%20choose%20between%20PV%20or%20TC%20until%204%20PV%20are%20assigned%2C%20then%20that%20would%20be%20perfect.%20Then%20all%20the%20overflow%20would%20be%20TC.%3CBR%20%2F%3E%3CBR%20%2F%3EAgain%2C%20Idk%20if%20this%20is%20possible%20but%20I'm%20a%20lot%20closer%20than%20where%20I%20was%20before.%20Appreciate%20any%20help%20or%20guidance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677168%22%20slang%3D%22en-US%22%3ERe%3A%20Randomly%20select%20rows%20with%20criteria-based%20requirement%20across%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677168%22%20slang%3D%22en-US%22%3EYou%20must%20manually%20enter%20your%20desired%20results%2C%20then%20explain%20how%20you%20determined%20each%20so%20that%20the%20logic%20of%20the%20required%20formula%20may%20come%20to%20light.%3C%2FLINGO-BODY%3E
Highlighted
markraymond
New Contributor

See attached file. 

 

I'm trying to auto-assign some values based on criteria. 

 

Either select PV or TC or LB with certain requirements

  • Have at least 14 columns with reps
  • Assign a certain number of PV or TC at certain times of the day. The overflow would be auto-assigned to TC or LB
  • LB is only assigned at certain times
  • IF LB equals 0, no need to assign

I've tried to use =Choose or =RAND but to no avail. Is this even possible to complete? 

2 Replies
Highlighted

UPDATE: So I have this thus far: =CHOOSE(RANDBETWEEN(1,2), "PV", "TC") 

If I could choose PV or TC based on a criteria e.g. only assign 4 PV but randomly choose between PV or TC until 4 PV are assigned, then that would be perfect. Then all the overflow would be TC.

Again, Idk if this is possible but I'm a lot closer than where I was before. Appreciate any help or guidance. 

Highlighted
You must manually enter your desired results, then explain how you determined each so that the logic of the required formula may come to light.
Related Conversations
Force to select a staff member at bookings
koppenburg in Microsoft Bookings on
1 Replies
How to SUM Data from non-regular rows
Andy_Przybysz in Excel on
1 Replies
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies