Using RAND function

ToyMiller

New Member
Joined
Feb 12, 2019
Messages
12
Question, I have not used this and I need to set up based on 2 criteria

I have a column with names in it and a second column with codes in them.

I only want 10% of the total codes for each name

Name 1 can have 20 codes so I need to randomize the two codes that get audited.

Name 2 50 codes so randomize 5 codes to audit and so on.

Right now I am using a Pivot to give me the 10% of codes to audit then I have a list with name and code and I am using:

=RAND() in one column and in the next I am using INDEX(B2:B34,RANK,(C2,C2:B34),1) and copying this down the number that represents the 10%

Any help would be appreciated.

T
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
OP PM'd me with some extra info on this problem so am copying it here to help anyone solving this.

"Basically I have a column with stores, the second column has a specific identifier. Each store will have a different number of identifiers.

Store 1 64545
Store 1 49196
Store 1 61983
Store 2 14564
Store 2 74565
Store 2 95641
Store 2 65945

And so on, I want to take 10 of each stores total number of Identifiers and randomize from the 10% the identifiers they have to audit. "
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Ok you havent got 10 identifiers in your additional example data so let's change the 10 to 2.

So based on your example data are you saying
you have 2 stores with 3 or 4 identifiers and you want to pick 2 identifiers randomly for each store?
 
Last edited:

ToyMiller

New Member
Joined
Feb 12, 2019
Messages
12
Ok you havent got 10 identifiers in your additional example data so let's change the 10 to 2.

So based on your example data are you saying
you have 2 stores with 3 or 4 identifiers and you want to pick 2 identifiers randomly for each store?

Correct, in this sample yes, however, I can have 500 - 1000 identifiers per store and I am only looking to randomly grab 10% of those identifiers.

T
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350

ADVERTISEMENT

OK, cant get this work entirely but am wondering if you can base something on this.

Data consists of 2 columns (store numbers and identifiers) and 7 rows

in C1:C7 put/copy
=RAND()

in D1:D7 put/copy
=INDEX(A$1:B$7,MATCH(AGGREGATE(15,6,D$1:D$7,ROW()),D$1:D$7,0),1)

in E1:E7 put/copy
=INDEX(A$1:B$7,MATCH(AGGREGATE(15,6,D$1:D$7,ROW()),D$1:D$7,0),2)

This will produce a list of random stores (column D) and random identifiers (column E) without repeats.

That just leaves a way of selecting stores from that list that are less than the required limits.
Which is where I'm stuck :(
 

ToyMiller

New Member
Joined
Feb 12, 2019
Messages
12
OK, cant get this work entirely but am wondering if you can base something on this.

Data consists of 2 columns (store numbers and identifiers) and 7 rows

in C1:C7 put/copy
=RAND()

in D1:D7 put/copy
=INDEX(A$1:B$7,MATCH(AGGREGATE(15,6,D$1:D$7,ROW()),D$1:D$7,0),1)

in E1:E7 put/copy
=INDEX(A$1:B$7,MATCH(AGGREGATE(15,6,D$1:D$7,ROW()),D$1:D$7,0),2)

This will produce a list of random stores (column D) and random identifiers (column E) without repeats.

That just leaves a way of selecting stores from that list that are less than the required limits.
Which is where I'm stuck :(

The above accomplished the heavy lifting, now I will apply this to the bigger data set and then, figuring out how to only display the 10%.

Thank you, I will keep working on my end as I hope you will do the same to get the 10% answered

-T
 

ToyMiller

New Member
Joined
Feb 12, 2019
Messages
12
I want to ask this a different way now. After thinking through this more, I have a list of Stores, I have a list of products that need to be randomized. Based on the volume of products from the stores I need to randomize the products and only take 10% of that number per store to put in a list. That list (example, can be 366 total) divide that by 4 ppl and they have 92 products to audit.

Go.......
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top