distribute randomly

Dear Experts,
I have problem to distribute column "A" values to other columns randomly.
I want to distribute values to columns randomly by one. Worksheet will look like this:
 
A     C1      C2   C3    C4     C5   C6     C7   C8    C9     C10
7        1               1      1      1              1      1                1
3                 1                                      1              1
15     1        2      2      1      2     1       1      1      2        2

I used worksheet to better describe a problem. Any sollution (sql, access, excel, vba) is acceptible.
Real data set has 50 columns and about 300 rows.

Thanks,
Milo
Grbic_MAsked:
Who is Participating?
 
SteveCommented:
OK, have re-written the code slightly:

Option Explicit

Function RandDistribute(ToDistribute As Long, NoColumns As Long) As Variant
Dim HoldArray() As Long
Dim x As Long, y As Long, z As Long
Dim ColumnString As String
Dim theColumns As Variant

ReDim HoldArray(1 To NoColumns)

y = WorksheetFunction.RoundDown(ToDistribute / NoColumns, 0)
z = ToDistribute - (NoColumns * y)

For x = 1 To NoColumns
    If x = 1 Then
        ColumnString = x
    Else
        ColumnString = ColumnString & "|" & x
    End If
    HoldArray(x) = y
Next x

For x = 1 To z
    theColumns = Split(ColumnString, "|")
    y = WorksheetFunction.RandBetween(0, UBound(theColumns))
    HoldArray(theColumns(y)) = HoldArray(theColumns(y)) + 1
    
    If y = UBound(theColumns) Then
        ColumnString = Left(ColumnString, Len(ColumnString) - (Len(theColumns(y)) + 1))
    ElseIf y = 0 Then
        ColumnString = Right(ColumnString, Len(ColumnString) - (Len(theColumns(y)) + 1))
    Else
        ColumnString = Replace(ColumnString, "|" & theColumns(y) & "|", "|")
    End If

Next x

RandDistribute = HoldArray

End Function

Open in new window


and created some code to put the formula into column B and across as many as needed:

Sub PutIntoSelection()

NoCol = InputBox("Enter Number Of Columns")
NoRow = InputBox("Enter Row Number")

If NoCol <> 0 And IsNumeric(NoCol) Then
    Range(Cells(NoRow, 2), Cells(NoRow, NoCol + 1)).FormulaArray = "=RandDistribute(RC[-1]," & NoCol & ")"
End If

End Sub

Open in new window


2003 would likely need the "analysis toolpack addin" loading as some functions are now standard in 2007 which were not in 2003.

The function it's self if not being loaded using the code above is an array function.
It must be an array function to return all 70 results at once (or the function just couldnt work)
To enter the array function manually:
1) Highlight all the cells you wish to put the formula into from left to right.
2) Click into the formula bar at the top of the screen
3) Type equals then the function name followed by open bracket "=RandDistribute("
4) Click on the cell with the value to distribute
5) type comma then the number of columns across you have selected then close bracket
    Formula should look like =RandDistribute(A2,70)
6) Press [CTRL]+[SHIFT]+[ENTER] (this tells excel that all the selected cells have the same formula in which will return an array of data (one cell per formula value)

You should now be able to copy the rows down as far as you need to.
*** important note *** the values will recalculate on change of the value in column 'A'
So once happy with the result, you may need to copy and paste the values to "fix" them as static

This should hopefully get you on track.

ATB
Steve.
RandDistribute.xlsm
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Use the below SQL Server T-SQL to select random 1's and NULL's (you tell us if you want an empty string '', 0, or if a NULL is okay.
  SELECT 
	CASE WHEN Rand() > 0.5 THEN 1 END as C1, 
	CASE WHEN Rand() > 0.5 THEN 1 END as C2, 
	CASE WHEN Rand() > 0.5 THEN 1 END as C3, 
	CASE WHEN Rand() > 0.5 THEN 1 END as C4

Open in new window

Also, retarding the 1's and 2's, you'll have to spell out the logic for how to generate this.

Also, I published an article awhile back on How to create a million sample people that uses lots of Rand() functions.  Feel free to go nuts with it, and if you like it, hit 'Yes' at the bottom of the article.
0
 
Kent DyerIT Security Analyst SeniorCommented:
How about using:

=RANDBETWEEN(1,A1)
=RANDBETWEEN(1,A2)
=RANDBETWEEN(1,A3)

Where:
A1=7
A2=3
A13=15

Then, just using the Autofill to the columns you need?

HTH,

Kent
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Grbic_MAuthor Commented:
To clarify. I want to spread A1=7 to 7 columns randomly as 1; A2=3 to 3 columns, A3=15 to 10 columns and the rest will be spread to 5 columns randomly. Some col will get 2.
0
 
Anthony PerkinsCommented:
Topics:Microsoft Excel Spreadsheet Software ,MS SQL Server
I am still trying to figure out what this has to do with SQL Server.
0
 
Grbic_MAuthor Commented:
Topic is irrelevant for me.
0
 
SteveCommented:
the following Function may do what you need:
(should be entered as [ctrl]+[shift]+[enter] in the cells to output to.

Function RandDistribute(ToDistribute As Long, NoColumns As Long) As Variant
Dim HoldArray() As Long
ReDim HoldArray(1 To NoColumns)

y = WorksheetFunction.RoundDown(ToDistribute / NoColumns, 0)
Z = ToDistribute - (NoColumns * y)

For x = 1 To NoColumns
    If x = 1 Then
        ColumnString = x
    Else
        ColumnString = ColumnString & "|" & x
    End If
    HoldArray(x) = y
Next x

For x = 1 To Z
    theColumns = Split(ColumnString, "|")
    y = WorksheetFunction.RandBetween(0, UBound(theColumns))
    HoldArray(theColumns(y)) = HoldArray(theColumns(y)) + 1
    If y = UBound(theColumns) Then
        ColumnString = Replace(ColumnString, "|" & theColumns(y), "")
    Else
        ColumnString = Replace(ColumnString, theColumns(y) & "|", "")
    End If
Next x

RandDistribute = HoldArray

End Function

Open in new window


The attached workbook has the function as an example.
RandDistribute.xlsm
0
 
Grbic_MAuthor Commented:
Function works properly with default setup in Excel 2007. Is it any reason why does not in Excel 2003? How to add more columns? I am getting error #value! for more columns ( I tested on 70 columns). Works only if A2 is multiplier of the columns. Almost there.
0
 
Grbic_MAuthor Commented:
Thanks for instructions and code update. Looks fine now.
Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.