As posted in your multi-post in .programming
(pl don't multi-post)
--------------
Kashyap said:
.. 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$4>J2,),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
---