Distribution the number

M

MAHMOUD-LEE

Active Member
Joined
Feb 16, 2013
Messages
259
How can the distribution of the numbers in the table ?
Distribution the number
numDistribution1
1201
2221
3151
4141
5161
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
4
4
4
4
4
4
4
4
4
4
4
4
4
4
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
This should do it
Code: 
Sub Standard()

Dim C As Range
Range("d2").Select
For Each C In ActiveSheet.Range("a3:a" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)

    Num1 = C.Value
    Countnum = C.Offset(0, 1).Value
    I = 0
    Do Until I = Countnum
    ActiveCell.Value = Num1
    ActiveCell.Offset(1, 0).Select
    I = I + 1
    Loop

Next C

End Sub
 
Upvote 0
Assume num is column A and Distribution is column B. Using a VBA macro.
Code: 
Sub dist()
Dim lr As Long
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = lr To 2 Step -1
            .Cells(i + 1, 1).Resize(.Cells(i, 2).Value - 1, 1).EntireRow.Insert
            .Cells(i, 1).Resize(.Cells(i, 2).Value, 1) = .Cells(i, 1).Value
        Next
End With
End Sub
This code will insert rows based on the number in column B and will then fill that number of cell in column A with the corresponding value in column A for that item. The code should be copied to and run from the standard code module. Be sure the sheet you want to make the distribution on is the active sheet when the code is run. You should test this first on a copy of your sheet or a mock up.
 
Upvote 0
Thank you Mr. "Shadow12345"
Thank you Mr. "JLGWhiz"
On this wonderful codes
But I want the solution by the formula
 
Upvote 0
Hi Mahmoud,

Im not sure I know how to do that with just a formula... maybe some kind of index formula but would use vba for this kind of thing so i don't really know how.
 
Upvote 0
Thank you very much Mr. "Shadow12345" to help me
And your solution through code wonderful
Thank you for the second time
 
Upvote 0
Just as a point of interest, this can be done without using any loops...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose(Evaluate(Replace("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", "#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub
 
Upvote 0
Rick Rothstein said:
Just as a point of interest, this can be done without using any loops...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose(Evaluate(Replace("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", "#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub
Click to expand...




WHOOSSHHHHH! That was the sound of that one going right over my head. ... let me have a look at that. Split(not used it, but doesn't look hard), Join(not used it, but doesn't look hard), Transpose(got that bit), ... I give up that formula looks a monster. I really need to go read about the Ubound and variants I think, sounds like i could do some fun things with it.
 
Upvote 0
One possible formula approach:

E4 =INDEX(A$2:A$7,MATCH(1+ROW()-ROW(E$4),C$2:C$7,1))
copied down as many rows as necessary

Excel 2010
ABCDE
1NumDistributionCumulative
21201
322221
4315431
5414581
6516721
7881
81
91

<tbody>
</tbody>
Sheet1​
 
Upvote 0
shadow12345 said:
WHOOSSHHHHH! That was the sound of that one going right over my head. ... let me have a look at that. Split(not used it, but doesn't look hard), Join(not used it, but doesn't look hard), Transpose(got that bit), ... I give up that formula looks a monster. I really need to go read about the Ubound and variants I think, sounds like i could do some fun things with it.
Click to expand...
See if the following step-by-step explanation helps any...


This is the Excel formula to concatenate Column A values with a comma concatenated after it the number of times specified in Column B where I have used a # sign to stand in for the last row containing data...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose(Evaluate(Replace[COLOR=#000000]("IF(ROW(),[/COLOR][COLOR=#ff0000][B]REPT(A2:A#" & _
         "&"","",B2:B#)[/B][/COLOR][COLOR=#000000])"[/COLOR], "#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub


Functions that return text are not array-aware, so in order to make this an array formula we must embed the above formula in a structure that includes an array-aware function, so I use the ROW function inside an IF function (ROW always returns a values which IF sees as TRUE, so I did not include a FALSE section for the IF)...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose(Evaluate(Replace[COLOR=#000000]("[/COLOR][COLOR=#ff0000][B]IF(ROW(),[/B][/COLOR][COLOR=#000000]REPT(A2:A#" & _
         "&"","",B2:B#)[/COLOR][COLOR=#ff0000][B])[/B][/COLOR][COLOR=#000000]", [/COLOR]"#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub


The VB Replace function is used to change the # signs into the row number of the last data cell (the green part calculates the last row with data)...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose(Evaluate([COLOR=#ff0000][B]Replace("[/B][/COLOR][COLOR=#000000]IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))[/COLOR][COLOR=#ff0000][B]", "#", [/B][/COLOR][COLOR=#008000][B]Cells(Rows.Count, "A").End(xlUp).Row[/B][/COLOR][COLOR=#ff0000][B])[/B][/COLOR])), ""), ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub


The Evaluate function is used to process the text string representation of the resulting array formula...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose([COLOR=#ff0000][B]Evaluate([/B][/COLOR]Replace[COLOR=#000000]("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", [/COLOR]"#", Cells(Rows.Count, "A").End(xlUp).Row)[COLOR=#ff0000][B])[/B][/COLOR]), ""), ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub


We transpose this which turns it into a one-dimensional array because Join only works with one-dimensional arrays (Evaluate returns a two-dimensional array even for a single column of value)...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join([COLOR=#ff0000][B]Application.Transpose([/B][/COLOR]Evaluate(Replace[COLOR=#000000]("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", [/COLOR]"#", Cells(Rows.Count, "A").End(xlUp).Row))[COLOR=#ff0000][B])[/B][/COLOR], ""), ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub


We join the elements of the array into a text string using an empty string ("") as the delimiter (because the REPT function in the first explanation included the comma at the end)...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split([COLOR=#ff0000][B]Join([/B][/COLOR]Application.Transpose(Evaluate(Replace[COLOR=#000000]("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", [/COLOR]"#", Cells(Rows.Count, "A").End(xlUp).Row)))[COLOR=#ff0000][B], "")[/B][/COLOR], ",")
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub


We now split that long string into individual number by specifying the comma as the delimiter and assign the resulting one-dimensional array to a Variant variable ...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  [COLOR=#ff0000][B]Nums = Split([/B][/COLOR]Join(Application.Transpose(Evaluate(Replace[COLOR=#000000]("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", [/COLOR]"#", Cells(Rows.Count, "A").End(xlUp).Row))), "")[COLOR=#ff0000][B], ",")[/B][/COLOR]
  Range("C2:C" & UBound(Nums)) = Application.Transpose(Nums)
End Sub


Next we transpose the one-dimensional array stored in Nums to make it a column oriented array...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose(Evaluate(Replace[COLOR=#000000]("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", [/COLOR]"#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), ",")
  Range("C2:C" & UBound(Nums)) = [COLOR=#ff0000][B]Application.Transpose(Nums)[/B][/COLOR]
End Sub


And assign that to the output range. Note that Split produced a zero-based array, so theoretically there were UBound(Nums)+1 array elements in it, but we don't output the last element because it is always the empty string (Split created that empty element because the text string that was created by the Join function earlier ended with a comma which was the delimiter used to split the text)...
Code: 
Sub DistributeNumbers()
  Dim Nums As Variant
  Nums = Split(Join(Application.Transpose(Evaluate(Replace[COLOR=#000000]("IF(ROW(),REPT(A2:A#" & _
         "&"","",B2:B#))", [/COLOR]"#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), ",")
  [COLOR=#ff0000][B]Range("C2:C" & UBound(Nums))[/B][/COLOR] = Application.Transpose(Nums)
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Optimise pallet packing
Replies
4
Views
216
felixstraube
F
S
  • Solved
VBA Code Help
Replies
4
Views
269
Peter_SSs
Peter_SSs
M
  • Solved
Automating episode numbers when date changes
Replies
6
Views
96
Keebler
Keebler
L
  • Question
Finding the minimum cost for supply&demand distribution
Replies
0
Views
101
Legacy 143009
L
G
  • Question
Transpose tables sorted by performance order
Replies
1
Views
120
jimrward
jimrward

Forum statistics

Threads
1,214,642
Messages
6,120,700
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top