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.

Hi,

what exactly do you mean by "on an average"? For example, if you say that a
proportional number of items must come from each group (proportional to the
total size of each group) your selection is no longer random.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


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

Hi Shane,

That is exactly what I want..proportional number of items must come from
each group (proportional to the total size of each group)

- Kashyap


"Shane Devenshire" wrote:

Hi,

what exactly do you mean by "on an average"? For example, if you say that a
proportional number of items must come from each group (proportional to the
total size of each group) your selection is no longer random.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


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

As posted in your multi-post in .programming
(pl don't multi-post)
--------------
"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
---

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

Hi Max,

I'm not getting any value in column L. I'm getting the error '#NAME?'

and I tried adding 2 more names in Col B and Ref# in col A, but I'm unable
to get those 2 names in Col K even after editing formulas

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


Edited - =INDEX(E$2:E$6,MATCH(TRUE,INDEX(H$2:H$6=J2,),0))

and accordingly..

Pls help

Thanks,

Kashyap



"Max" wrote:

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