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.

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

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

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

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.

=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

I am still trying to figure out what this has to do with SQL Server.

(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
```

The attached workbook has the function as an example.

RandDistribute.xlsm

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.

Open in new window

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

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