Using RAND function

[ToyMiller]

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

 

[Special-K99]

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]

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?

 

[ToyMiller]

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]

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]

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]

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.......