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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
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
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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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.
Grbic_MAuthor Commented:
Topic is irrelevant for me.
SteveCost AccountantCommented:
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
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.
SteveCost AccountantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Grbic_MAuthor Commented:
Thanks for instructions and code update. Looks fine now.
Cheers!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.