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

[all4excel]

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

 

[Akashwani]

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
	A	B	C	D	E
1	Name	Check	Call	R=	R
2	Bill	R	26	1	*
3	Bob	*	*	0	*
4	Dan	R	25	2	*
5	Tim	R	25	3	*
6	Sam	*	*	0	*
7	Jan	*	*	0	*
8	Jo	R	25	4	*
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
	A	B	C	D	E	F	G
1	Rank	Rand	Call	*	VLookup *Table	*	*
2	4	0.3572	25	*	4	0.166761	25
3	2	0.739847	25	*	3	0.265037	25
4	1	0.945846	25	*	2	0.607055	25
5	3	0.523573	26	*	1	0.684657	26
Sheet2

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

I then recorded this macro...

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]

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]

Hi all4excel.

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

Good luck.

Ak

 

[all4excel]

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