Pick Ref# randomly on an average
 

Hi I have 2 columns as below..

Col A Col B

Ref# | Name
4565 | ABC
6585 | ABC
4314 | ABC
5648 | MNO
6546 | MNO
8974 | XYZ
1346 | MNO
6584 | XYZ
1366 | ABC
4985 | XYZ
1346 | XYZ
5456 | XYZ

I want to pick Ref# randomly on an average.

Say, in column B there are 40 ABC, 55 MNO, 80 XYZ, 10 PQR and so on.. Then I
need to pick a total on 20 ref# where I have ref# from all the Names on an
average.

Pick Ref# randomly on an average
 

Interesting question. In C1 enter:
=RAND() and copy down

Pick another column, say E. In E1 enter:
=A1
In E2 enter:
=IF(COUNTIF($B$1:B2,B2) 1,"",A2) and copy down. we see:

4565 ABC 0.649004509 4565
6585 ABC 0.084606441
4314 ABC 0.131187935
5648 MNO 0.608040285 5648
6546 MNO 0.774962504
8974 XYZ 0.118081037 8974
1346 MNO 0.885189386
6584 XYZ 0.656284418
1366 ABC 0.580591399
4985 XYZ 0.199459732
1346 XYZ 0.105358721
5456 XYZ 0.939330574

As you see, column E "picks" a sample from each Ref#. Always the first
sample. But we sant a random sample so sort columns A thru C by C:

6585 ABC 0.658213589 6585
1346 XYZ 0.323008196 1346
8974 XYZ 0.043325857
4314 ABC 0.526986275
4985 XYZ 0.970380988
1366 ABC 0.037955305
5648 MNO 0.743534431 5648
4565 ABC 0.376891038
6584 XYZ 0.007517221
6546 MNO 0.462970369
1346 MNO 0.71457043
5456 XYZ 0.474824674


Now the data in column E is a random sample of the data in column A, one
sample for each Ref#
--
Gary''s Student - gsnu200832


"Kashyap" wrote:

Hi I have 2 columns as below..

Col A Col B

Ref# | Name
4565 | ABC
6585 | ABC
4314 | ABC
5648 | MNO
6546 | MNO
8974 | XYZ
1346 | MNO
6584 | XYZ
1366 | ABC
4985 | XYZ
1346 | XYZ
5456 | XYZ

I want to pick Ref# randomly on an average.

Say, in column B there are 40 ABC, 55 MNO, 80 XYZ, 10 PQR and so on.. Then I
need to pick a total on 20 ref# where I have ref# from all the Names on an
average.

Pick Ref# randomly on an average
 

Hi,

Proportional number of items must come from each group (proportional to the
total size of each group)

- Kashyap


"Gary''s Student" wrote:

Interesting question. In C1 enter:
=RAND() and copy down

Pick another column, say E. In E1 enter:
=A1
In E2 enter:
=IF(COUNTIF($B$1:B2,B2) 1,"",A2) and copy down. we see:

4565 ABC 0.649004509 4565
6585 ABC 0.084606441
4314 ABC 0.131187935
5648 MNO 0.608040285 5648
6546 MNO 0.774962504
8974 XYZ 0.118081037 8974
1346 MNO 0.885189386
6584 XYZ 0.656284418
1366 ABC 0.580591399
4985 XYZ 0.199459732
1346 XYZ 0.105358721
5456 XYZ 0.939330574

As you see, column E "picks" a sample from each Ref#. Always the first
sample. But we sant a random sample so sort columns A thru C by C:

6585 ABC 0.658213589 6585
1346 XYZ 0.323008196 1346
8974 XYZ 0.043325857
4314 ABC 0.526986275
4985 XYZ 0.970380988
1366 ABC 0.037955305
5648 MNO 0.743534431 5648
4565 ABC 0.376891038
6584 XYZ 0.007517221
6546 MNO 0.462970369
1346 MNO 0.71457043
5456 XYZ 0.474824674


Now the data in column E is a random sample of the data in column A, one
sample for each Ref#
--
Gary''s Student - gsnu200832


"Kashyap" wrote:

Hi I have 2 columns as below..

Col A Col B

Ref# | Name
4565 | ABC
6585 | ABC
4314 | ABC
5648 | MNO
6546 | MNO
8974 | XYZ
1346 | MNO
6584 | XYZ
1366 | ABC
4985 | XYZ
1346 | XYZ
5456 | XYZ

I want to pick Ref# randomly on an average.

Say, in column B there are 40 ABC, 55 MNO, 80 XYZ, 10 PQR and so on.. Then I
need to pick a total on 20 ref# where I have ref# from all the Names on an
average.

Pick Ref# randomly on an average
 

Hi,

proportional number of items must come from each group (proportional to the
total size of each group)



"Gary''s Student" wrote:

Interesting question. In C1 enter:
=RAND() and copy down

Pick another column, say E. In E1 enter:
=A1
In E2 enter:
=IF(COUNTIF($B$1:B2,B2) 1,"",A2) and copy down. we see:

4565 ABC 0.649004509 4565
6585 ABC 0.084606441
4314 ABC 0.131187935
5648 MNO 0.608040285 5648
6546 MNO 0.774962504
8974 XYZ 0.118081037 8974
1346 MNO 0.885189386
6584 XYZ 0.656284418
1366 ABC 0.580591399
4985 XYZ 0.199459732
1346 XYZ 0.105358721
5456 XYZ 0.939330574

As you see, column E "picks" a sample from each Ref#. Always the first
sample. But we sant a random sample so sort columns A thru C by C:

6585 ABC 0.658213589 6585
1346 XYZ 0.323008196 1346
8974 XYZ 0.043325857
4314 ABC 0.526986275
4985 XYZ 0.970380988
1366 ABC 0.037955305
5648 MNO 0.743534431 5648
4565 ABC 0.376891038
6584 XYZ 0.007517221
6546 MNO 0.462970369
1346 MNO 0.71457043
5456 XYZ 0.474824674


Now the data in column E is a random sample of the data in column A, one
sample for each Ref#
--
Gary''s Student - gsnu200832


"Kashyap" wrote:

Hi I have 2 columns as below..

Col A Col B

Ref# | Name
4565 | ABC
6585 | ABC
4314 | ABC
5648 | MNO
6546 | MNO
8974 | XYZ
1346 | MNO
6584 | XYZ
1366 | ABC
4985 | XYZ
1346 | XYZ
5456 | XYZ

I want to pick Ref# randomly on an average.

Say, in column B there are 40 ABC, 55 MNO, 80 XYZ, 10 PQR and so on.. Then I
need to pick a total on 20 ref# where I have ref# from all the Names on an
average.

Pick Ref# randomly on an average
 

"Kashyap" wrote:
.. proportional number of items must come from each group
(proportional to the total size of each group)

Here's a formulas model which delivers it ..

Source data as posted assumed in A2:B13
In C2, copied down: =COUNTIF(B$2:B2,B2)

Create the reference weightage table
List the unique names in E2:E4 :
ABC
MNO
XYZ

In F2: =COUNTIF(B:B,E2)
In G2: =F2/SUM(F$2:F$4)
In H2: =SUM(G$2:G2)
Copy F2:H2 down to H4

H2:H4 serves as the cumulative weightage range,
ie the reference for the proportional aspect that is sought

Assume we want to pick 5 ref#s
In J2, copied to J6: =RAND()

Then
In K2, normal ENTER to confirm:
=INDEX(E$2:E$4,MATCH(TRUE,INDEX(H$2:H$4J2,),0))

In L2, normal ENTER to confirm:
=RANDBETWEEN(1,INDEX(F$2:F$4,MATCH(K2,E$2:E$4,0)))

In M2, normal ENTER to confirm:
=INDEX(A$2:A$13,MATCH(1,INDEX((B$2:B$13=K2)*(C$2:C $13=L2),),0))
Copy K2:M2 down to M6

Some explanations - what's happening:
1. Col J generates the core randomizations
2. Col K then picks up the names based on the cumulative weightage ranges
that the random nums generated in col J fall within
3. Since each name is associated with a number of ref#s, col L serves to
random pick from amongst the ref#s for any particular name, going by the
counts of the ref#s in F2:F4 which is used as the upper limit in randbetween.
Col L randomizes the relative serial numbers for the ref#s associated with
that name.
4. Col M then returns the required final outputs, ie the ref#s based on the
dual criteria randomized in cols K & L

Press F9 to regenerate ..

It is possible that repeat ref#s are generated in the output range as the
number of ref#s associated with each name is quite small in the sample data
(ie the Randbetween's range is small).

Do a high-five if above helped in any way, press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Pick Ref# randomly on an average
 

Slight errata:

In K2, normal ENTER to confirm:
=INDEX(E$2:E$4,MATCH(TRUE,INDEX(H$2:H$4=J2,),0))

"=" instead of ""
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Pick Ref# randomly on an average
 

Here's a nice working sample to demonstrate the suggested set-up:
http://freefilehosting.net/download/4575g
Weighted Randomization Model.xls

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Pick Ref# randomly on an average
 

Hello Max,

1. I think the OP asks for exactly the 4/12 of ABC hits in the output
not only 4/12 likehood. So you would need something like the D'Hondt
election distribution approach to define the number of ABC hits in the
output (easy for 50% ABC and 50% XYZ on a selection of 100 but what if
you have 99? You can only take 49 ABC's and 49 XYZ's and randomly
distribute the remaining one, I guess). The initial unique list and
frequencies you can get with my UDF lfreq, for example.

2. The OP obviously does not want to come up with dupe ref# at the
end. Your example does not ensure this. I suggest to take my UDF
UniqRandInt for this.

Regards,
Bernd

www.sulprobil.com

Pick Ref# randomly on an average
 

Hi Bernd,

Suggest to dovetail discussions into the OP's thread in
..worksheet.functions,
http://tinyurl.com/ab6t2z

I've just posted my last follow-through there with a way to address the OP's
non-dups requirement.

You might wish to re-post your comments/alternative offers to the OP over
there.

.. Your example does not ensure this ..
Agreed, and I did mention this explicitly in my response detailing the
modeling steps to the OP.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Bernd P" wrote in message
...
Hello Max,

1. I think the OP asks for exactly the 4/12 of ABC hits in the output
not only 4/12 likehood. So you would need something like the D'Hondt
election distribution approach to define the number of ABC hits in the
output (easy for 50% ABC and 50% XYZ on a selection of 100 but what if
you have 99? You can only take 49 ABC's and 49 XYZ's and randomly
distribute the remaining one, I guess). The initial unique list and
frequencies you can get with my UDF lfreq, for example.

2. The OP obviously does not want to come up with dupe ref# at the
end. Your example does not ensure this. I suggest to take my UDF
UniqRandInt for this.

Regards,
Bernd

www.sulprobil.com