Distribute randomly or by percentage in the range.

[motilulla]

Using Excel 2010
Hello,

I have 16 set in the range F6:F21. Which I want to distribute randomly or by percentage within range D6:D90. Please suggest any VBA or formula.

Note: Distribution range can be varying from this example 85 to any may be 2500.

Excel Questions.xlsm
	A	B	C	D	E	F	G
1
2
3
4				Distribute
5			85	Rand Or %		16
6			1			00
7			2			01
8			3			02
9			4			0M
10			5			10
11			6			11
12			7			12
13			8			1M
14			9			20
15			10			21
16			11			22
17			12			2M
18			13			M0
19			14			M1
20			15			M2
21			16			MM
22			17
23			18
24			19
25			20
26			21
27			22
28			23
29			24
30			25
31			26
32			27
33			28
34			29
35			30
36			31
37			32
38			33
39			34
40			35
41			36
42			37
43			38
44			39
45			40
46			41
47			42
48			43
49			44
50			45
51			46
52			47
53			48
54			49
55			50
56			51
57			52
58			53
59			54
60			55
61			56
62			57
63			58
64			59
65			60
66			61
67			62
68			63
69			64
70			65
71			66
72			67
73			68
74			69
75			70
76			71
77			72
78			73
79			74
80			75
81			76
82			77
83			78
84			79
85			80
86			81
87			82
88			83
89			84
90			85
91
92
Hoja1

Regards,
Moti

 

[motilulla]

Hello, searching in the excel forums I found under this thread similar solution
Random Letters
Which I was able to modified as per my need as below…Please also I want can someone check is it correct modified?

Sub DistributeRandomLettersNoRepeatsInRows()
  Dim X As Long, RandomIndex As Long, row As Range, TempElement As String, Arr() As String
 
    Range("D6:D2500").ClearContents
    
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).row
  
    Arr = Split("00 01 02 0M 10 11 12 1M 20 21 22 2M M0 M1 M2 MM")  ' Single space delimited list of letters
    For Each row In Range("D6:D" & lngLastRow).Rows
    For X = UBound(Arr) To 0 Step -1
      RandomIndex = Int((X - LBound(Arr) + 1) * Rnd + LBound(Arr))
      TempElement = Arr(RandomIndex)
      Arr(RandomIndex) = Arr(X)
      Arr(X) = TempElement
    Next
    row = (Arr)
  Next
End Sub

In the above macro, the 16 combinations array is built-in inside the macro I do not know how it can be selected via input box from cell range F6:F21.

Please help to make it possible via input box selection.

Regards,
Moti

 

[motilulla]

Help Please

 

[motilulla]

It seems that it is not possible to pick 16 option from sheet cell selection via input any other idea please suggest

 

[motilulla]

Please not I have open query also under this thread.

Selection via input help

Using Excel 2010 Hello, I have question under this link is it possible to get resolve here? https://www.mrexcel.com/board/threads/distribute-randomly-or-by-percentage-in-the-range.1253806/ Please help if possible. Regards,

www.excelforum.com

Regards,
Moti

 

[motilulla]

Hello, I found one one more option in this require to fill InputBox desire Patt with space...but still i need InputBox which give an option pattern could be selected from the sheet. is it possible?

'VBA code help! Removing unwanted columns using inputbox
'VBA code help! Removing unwanted columns using inputbox

Sub DistributeRandomLettersNoRepeatsInRows_1()
  Dim x As Long, RandomIndex As Long, row As Range, TempElement As String, Arr() As String, ibox As String
 
    Range("D6:D2500").ClearContents
    
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).row
  
    'Arr = Split("00 01 02 0M 10 11 12 1M 20 21 22 2M M0 M1 M2 MM")  ' Single space delimited list of letters
    
    ibox = InputBox("Enter Partido P15 separated by Single Space")
    
    If Len(ibox) = 0 Then
    Exit Sub
    Else
    Arr = Split(ibox, " ")
    
    For Each row In Range("D6:D" & lngLastRow).Rows
    For x = UBound(Arr) To 0 Step -1
      RandomIndex = Int((x - LBound(Arr) + 1) * Rnd + LBound(Arr))
      TempElement = Arr(RandomIndex)
      Arr(RandomIndex) = Arr(x)
      Arr(x) = TempElement
    Next
    row = (Arr)
  Next
 End If

End Sub

Regards,
Moti