Distribution the number

[MAHMOUD-LEE]

How can the distribution of the numbers in the table ?

			Distribution the number
num	Distribution		1
1	20		1
2	22		1
3	15		1
4	14		1
5	16		1
			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>

 

[shadow12345]

Thanks ever so much Rick.... its why I keep putting in my two cents worth... still always more to learn. I like it, im gonna go see if I can use this in one of my matching macro's i already have that could do with some speeding up.

 

[XOR LX]

Or, without a helper column, array formula**:

=IFERROR(INDEX($A$2:$A$6,MATCH(TRUE,MMULT(0+(ROW(INDEX(B:B,2):INDEX(B:B,6))>=TRANSPOSE(ROW(INDEX(B:B,2):INDEX(B:B,6)))),0+$B$2:$B$6)>=ROWS($1:1),0)),"")

Copy down until you start to get blanks.

Based on data being in A2:B6. Change the 2 and 6 if necessary.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[Rick Rothstein]

Just as a point of interest, this can be done without using any loops...

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

Okay, now I am just having fun (please do not think the following code is alright to use), but I figured out a way to make my macro a one-liner... it is kind of a long line of code, so I had to use line continuations to make it fit nicely within this forum's code window, but it is just a single line of code

Sub DistributeNumbers()
  Range("C2").Resize(Application.Sum(Range("B:B"))) = Application.Transpose(Split(Join(Application.Transpose( _
  Evaluate(Replace("IF(ROW(),REPT(A2:A#&"","",B2:B#))", "#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), ","))
End Sub

 

[Rick Rothstein]

Or, without a helper column, array formula**:

=IFERROR(INDEX($A$2:$A$6,MATCH(TRUE,MMULT(0+(ROW(INDEX(B:B,2):INDEX(B:B,6))>=TRANSPOSE(ROW(INDEX(B:B,2):INDEX(B:B,6)))),0+$B$2:$B$6)>=ROWS($1:1),0)),"")

Copy down until you start to get blanks.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Very nice! My gut told me that this could not be done with a formula, so I am happy to see your formula which proved my gut wrong. Kudos. It looks like one of these days I will have to wrap my head around that MMULT function!

The only downside I see to using a formula is knowing when to stop copying the formula down. You could know by putting in a formulas to sum Column B... that is how many rows you would need, but what if that total was, say, 10,000? That is a lot of copying down and a lot of array formulas.

 

[XOR LX]

Very nice! My gut told me that this could not be done with a formula, so I am happy to see your formula which proved my gut wrong. Kudos. The only downside I see to using a formula is knowing when to stop copying the formula down. You could know by putting in a formulas to sum Column B... that is how many rows you would need, but what if that total was, say, 10,000? That is a lot of copying down and a lot of array formulas.

Thanks, and agreed re the number of array formulas, which would be an issue whatever the set-up, so naturally your code will be the more efficient in a situation such as that (and most likely in all situations).

As for copying down, you could use a simple Go To after, as you say, summing column B, which is something I often do (rather than sitting there for several minutes trying to "drag" a formula down!)

Regards

 

[MAHMOUD-LEE]

Mister "Rick"
You're a genius, your solutions are always distinctive
Thank you very much Mr. "Rick"

 

[MAHMOUD-LEE]

Mister "XOR LX"
Excellent formula
This is exactly who I wanted
Thank you very much Mr. "XOR LX"

 

[XOR LX]

Mister "XOR LX"
Excellent formula
This is exactly who I wanted
Thank you very much Mr. "XOR LX"

You're welcome.

 

[markmzz]

Try this small modification in XOR LX's formula too:

=IFERROR(INDEX($A$2:$A$6,MATCH(1=1,ROWS(D$2:D2)<=MMULT(--(ROW($A$2:$A$6)-TRANSPOSE(ROW($A$2:$A$6))>=0),$B$2:$B$6),0)),"")

Markmzz

 

[XOR LX]

Try this small modification in XOR LX's formula too:

=IFERROR(INDEX($A$2:$A$6,MATCH(1=1,ROWS(D$2:D2)<=MMULT(--(ROW($A$2:$A$6)-TRANSPOSE(ROW($A$2:$A$6))>=0),$B$2:$B$6),0)),"")

Markmzz

Hi Markmzz,

I guess that's pretty much the same as mine except that I've now started using this INDEX construct with ROW. Makes it a bit longer and a few more function calls, but guards against row insertions (and doesn't use INDIRECT), so perhaps worth it?

Also, if there are any blanks in the range B2:B6, then e.g. 0+$B$2:$B$6 will prevent the formula from erroring.

Cheers