Pick Ref# randomly on an average

K

Kashyap

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

Shane Devenshire

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

Kashyap

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
 
M

Max

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)
Click to expand...

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

Kashyap

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
 
M

Max

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

Randbetween requires the Analysis Toolpak to be installed

You need to install and activate it ..
Check the "Analysis Toolpak" box via Tools > Add-Ins

Chip Pearson's page has details on the "Analysis Toolpak" at:
http://www.cpearson.com/excel/ATP.htm

No high-fives from you ?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 
K

Kashyap

Hi Max, I'm not getting the expected result..

I need proportional number of items from each group without any
duplicate.(proportional to the total size of each group)
 
M

Max

.. I'm not getting the expected result ..
.. need proportional number of items from each group without any
duplicate (proportional to the total size of each group)
Click to expand...

Why, that's exactly what the formulas driven model accomplishes,
except for this "new" requirement: without any duplicate
(I did mention this point as a caveat earlier)

In the sample, one way to get a handle on this non-dups aspect is to have an
eyeball alert via a formula to count the unique ref#s generated in the final
o/p range M2:M6 equated to the number of unique ref#s required (5)

Place this in say N1:
=SUMPRODUCT((M2:M6<>"")/COUNTIF(M2:M6,M2:M6&""))=5

N1 will return TRUE if there are no dups, FALSE otherwise
Format N1 in red, bold, larger font, whatever to make it outstanding

Just keep your eye trained on N1 as you tap F9 to regenerate
Stop tapping when you see TRUE in N1
You've got the required set of unique ref#s in M2:M6
Repeat to get the next set

Adapt the model set-up to suit your actuals

Still no high-fives? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
 
K

Kashyap

Hi Max,

Proportional numbers what I got in col F are as below.

ABC 6
XYZ 6
MNO 4
UTV 3


But, whan I press F9 proportion differs each time as below..

1
4
2
4

4
5
2
0

7
1
3
0

1
6
2
2

I tried this for 15 random number out of 19.. What I need is that
proprotions should not change..

ABC 6
XYZ 6
MNO 4
UTV 3

Thanks..
 
M

Max

Believe my interp & yours on your original core issues are quite different
But, when I press F9 proportion differs each time ..
Click to expand...
Of course, isn't the choosing of the names supposed to be random? The
"weightage" factor is still there, ie in the cumulative weightage range H2:H4
which provides the proportional reference/base for the name randomization
process. For example, XYZ has a 42% chance of being "picked" compared to
ABC's 33% and MNO's 25%. This 42% chance is actualized via its spread: 58% -
100%, ie any random num generated by RAND() [in col J] between 0.58 to 1.0
will "pick" XYZ as the random name. ABC and MNO has correspondingly lower
spreads: 0 - <33% (33%), and 33% - <58% (25%), hence are proportionately less
likely to be picked compared to XYZ by RAND()'s generation. And only after
the name is randomly chosen, would the ref#s associated with it be randomly
selected using randbetween.

If you don't want the names to be randomly chosen, then just allocate the
names by their ratios in F2:F5. In the sample, clear cols H, J and K.
Manually enter a mix of the 3 names into K2:K6, duplicating each name to
correspond roughly to their ratios in F2:F5. The rest of the set up/model
execution remains unchanged.

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

Chiranth

Ok, I have

Col A Col B
Ref# Status
31850261 ABC
32430586 MNO
31946557 MNO
32118825 MNO
32455687 MNO
32455477 XYZ
32430517 XYZ
31850189 UTV
32430664 ABC
32323468 MNO
32116014 XYZ
32490083 XYZ
32504837 UTV
32505148 ABC
32505411 MNO
32505828 XYZ
32513272 XYZ
32564298 UTV
32579326 ABC

Col C

1
1
2
3
4
1
2
1
2
5
3
4
2
3
6
5
6
3
4

Col E Col F Col G Col H

ABC 4 0.210526316 2
XYZ 6 0.315789474 3
MNO 6 0.315789474 3
UTV 3 0.157894737 2

So, I have 19 ref# as above and Total ABC=4, XYZ=6, MON=6, UTV=3.. I
want 10 random ref# (which are unique) to be picked in which ABC &
UTV=2, XYZ & MNO=3 (as per col H)

How do I do this?

Thanks



Believe my interp & yours on your original core issues are quite different
But, when I press F9 proportion differs each time ..
Click to expand...

Of course, isn't the choosing of the names supposed to be random? The
"weightage" factor is still there, ie in the cumulative weightage range H2:H4
which provides the proportional reference/base for the name randomization
process. For example, XYZ has a 42% chance of being "picked" compared to
ABC's 33% and MNO's 25%. This 42% chance is actualized via its spread: 58% -
100%, ie any random num generated by RAND() [in col J] between 0.58 to 1.0
will "pick" XYZ as the random name. ABC and MNO has correspondingly lower
spreads: 0 - <33% (33%), and 33% - <58% (25%), hence are proportionately less
likely to be picked compared to XYZ by RAND()'s generation. And only after
the name israndomlychosen, would theref#s associated with it berandomly
selected using randbetween.

If you don't want the names to berandomlychosen, then just allocate the
names by their ratios in F2:F5. In the sample, clear cols H, J and K.
Manually enter a mix of the 3 names into K2:K6, duplicating each name to
correspond roughly to their ratios in F2:F5. The rest of the set up/model
execution remains unchanged.

--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---

Kashyap said:
Hi Max,
Click to expand...
Proportional numbers what I got in col F are as below.
Click to expand...
ABC        6
XYZ                6
MNO        4
UTV        3
Click to expand...
But, whan I press F9 proportion differs each time as below..




I tried this for 15 random number out of 19.. What I need is that
proprotions should not change..
Click to expand...
ABC        6
XYZ        6
MNO        4
UTV        3
Click to expand...
Thanks..
Click to expand...
Click to expand...
 
M

Max

Chiranth/Kashyap,

Here's the revised model, adapted to suit
all of your actuals as posted below:

http://freefilehosting.net/download/45919
Weighted Randomization Model v2.xls

Rate this response in google & in MS' webpage
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
Ok, I have

Col A Col B
Ref# Status
31850261 ABC
32430586 MNO
31946557 MNO
32118825 MNO
32455687 MNO
32455477 XYZ
32430517 XYZ
31850189 UTV
32430664 ABC
32323468 MNO
32116014 XYZ
32490083 XYZ
32504837 UTV
32505148 ABC
32505411 MNO
32505828 XYZ
32513272 XYZ
32564298 UTV
32579326 ABC

Col C

1
1
2
3
4
1
2
1
2
5
3
4
2
3
6
5
6
3
4

Col E Col F Col G Col H

ABC 4 0.210526316 2
XYZ 6 0.315789474 3
MNO 6 0.315789474 3
UTV 3 0.157894737 2

So, I have 19 ref# as above and Total ABC=4, XYZ=6, MON=6, UTV=3.. I
want 10 random ref# (which are unique) to be picked in which ABC &
UTV=2, XYZ & MNO=3 (as per col H)

How do I do this?

Thanks
 
K

Kashyap

Thnks Max, everything seems to be working good.. But, can Col K be automated
as well? I can insert ratio in Col H (manually of with help of formula).

Also, may I have a macro to Tap F9 to regenerate until N1 shows TRUE..
Because all these will happen in the background. I have a different page as
fromt end.

Thnak you..
 
K

Kashyap

Hi Max,

If I increase the size of COL K,L,M to 20 and A,B,C to 40, I'm not getting
'TRUE' in N1 at all..
 
M

Max

Not sure whether you amended the eyeball alert N1's expression correctly?
viz: the output range monitored and the physical num that its equated to?

Eg in my sample ver2, it was written in N1:
=SUMPRODUCT((M2:M11<>"")/COUNTIF(M2:M11,M2:M11&""))=10
where M2:M11 = output range, and M2:M11 is a range that spans 10 cells

If the output range is now: M2:M21
N1 should be adapted to read:
=SUMPRODUCT((M2:M21<>"")/COUNTIF(M2:M21,M2:M21&""))=20

Btw, pl go back to ALL responses which helped you in this thread,
click the YES buttons in ALL responses.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 
M

Max

Well, if you had adjusted the eyeball alert N1's expression correctly -- as
highlighted in my earlier response, then one other possibility to try is to
simply to increase the param below in the sub (which Dave P gave to you in
your .programming query)

MaxCount = 1000

You could try increasing it to say:
MaxCount = 5000

Or, even:
MaxCount = 10000

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

comparing two fields in an excel spreadsheet 5
Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0

Top