Randomly Placing Fixed Values in Different Cells/Columns based on Ticks.

all4excel

Active Member
Joined
Feb 15, 2008
Messages
435
Randomly Placing Fixed Values in Different Cells/Columns based on Ticks.

Dear Excelliers,

I am trying to avoid Manual Work as I want to enter just 2 Different values one greater than the other in different Columns based on which ever columns have a Tick Selected in it..
<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=72><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 27pt" height=36><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 27pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=36 width=72>þ</TD></TR></TBODY></TABLE>

There are Totals of the Interested Prospects who needs to be followed up and this job is assigned to the Sales Team..however the no of Prospects keeps on changing each day and also the no of people involved to make this calls also can keep on changing depending on the People Present,Absent or Busy with other assignments..

So in order to make life easier we Put a Tick in the Column for the Values to be fed in the adjacent Column next to the TICK Column.

Example:
I get the Total Interested Prospects Data in the Column D for a particular Date ..

and I have decided to distribute these Prospects amongst a Team of 4 Sales Mgrs who will be calling then..

Math = TOtal Interested / 4 .
But if the Total Interested No is 101 then 101/4 = 25.25 which is not possible so what we do is we decide that 3 people out of the 4 call 25 people and the Fourth one calls 1 extra i.e 26 to complete the Target.

Now the Total No of Sales Team is more than 4 but depending on the OFfs and other assignments we would select who will be making these calls.

So, Lets say out of the 6-7 Team Mgrs I select Four People then I can Select anyone in any combination..

The No of Four People would help me get the Divisor and the Tick would help me understand who amongst the 6-7 People have been selected.

Now what I want is to put these 25,25,25,26 nos in the appropriate columns where there is a Tick.

Now the Values to be Allotted are Fixed but these need to be Randomised so that any of these Four can get the 25 or 26 and it should happen only once and immediately it needs to be converted to Values otherwise it will keep on changing everytime theres a change in the Sheet.

Hope the Concept is Clear..

So I just want to use the Randomization to put the values in different Columns based on their selections with TICKS.

Regards
all4excel
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

I'm not an expert so I cannot help you further with VBA code, sorry.

Is this something similar to what you want?....


Excel Workbook
ABCDE
1NameCheckCallR=R
2BillR261*
3Bob**0*
4DanR252*
5TimR253*
6Sam**0*
7Jan**0*
8JoR254*
Sheet1


If you format column B to Wingdings2 the R will become a tick within a box.

The formulas in C2 & D2 need to be copied down.

On Sheet2 you will need something like this....


Excel Workbook
ABCDEFG
1RankRandCall*VLookup *Table**
240.357225*40.16676125
320.73984725*30.26503725
410.94584625*20.60705525
530.52357326*10.68465726
Sheet2


The formulas in A2 & B2 need to be copied down.

I then recorded this macro...
Code:
Sub RandTable()
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Range("E2:G5").Select
    Selection.ClearContents
    Range("A2:C5").Select
    Selection.Copy
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("D1").Select
    Sheets("Sheet1").Select
Application.ScreenUpdating = True
End Sub

When this macro is run, the data in the Vlookup Table will change and so will the values in column C of Sheet1.

This definitely isn't the best way to solve your problem, but it does appear to work, all though additional cells need to be used as "Helper" cells/columns.

Here is the test workbook for you to look at....
all4excel.xls


If nothing else, then I've bumped you up for a real expert to look at :)

Good luck
 

all4excel

Active Member
Joined
Feb 15, 2008
Messages
435
Dear Akashwani,

Thanks for the Help, but that is not what I want though its very close..

I think I was unable to explain correctly and therefore that's causing some confusion..
You are very close but I don't intend to enter anything manually at all as I will be getting the Total Prospects which is Summation for all Not-Interested , Interested , Site Visit, Presentation options from a Different Sheet.

Now this number for the different Totals:
Total Not-Interested
Total Interested
Total Site Visit
Total Presentation
which will be the (Dividend)

and based on the Ticks next to the Sales Mgrs Decides the No of People amongst whom the Total No of Calls ( Dividend) and divided by the No of People which becomes the Divisor..

The Divisor is the No of People Selected by the Ticks.

Example:
If the Total for the Interested Prospects is 101 and the No of Sales people selected by Ticking them Manually is 4 then the Actual Division mathematically would give a result of 25.25 but though mathematically correct practically it will be incorrect as there needs to be only one Sales person who can call this prospect and not all Four..

So we need two Quotients such as 25 and 26 so now there can be 3 Sales People who can call 25 Prospects and one of them can call 26 Prospects which is 1 more than the remaining 3..

These values need to go in those columns next to the Ticked Columns.

So I want to Randomize the Placement for these Sales people Numbers i.e 25 and 26..

So there would be no Bias and any of the Selected Sales people can get these values.

Regards
all4excel
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi all4excel.

I'm sorry, but I don't have the solution you seek

Good luck.

Ak
 

all4excel

Active Member
Joined
Feb 15, 2008
Messages
435
Hi all4excel.

I'm sorry, but I don't have the solution you seek

Good luck.

Ak

Dont be, I sincerely appreciate your efforts, you did your best in whichever way you could and thats more important and commendable.

I think I need to paste some Images to give an Idea..


So will do that tommorow IST morning..
 

Watch MrExcel Video

Forum statistics

Threads
1,123,303
Messages
5,600,857
Members
414,407
Latest member
Zaner0445

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top