On 3/30/2010 7:53 PM, Bruce Sinclair wrote:
Hi

I often want to generate simple patterns of numbers, but have yet to find an
easy way of doing this in excel. It's really easy in minitab (for example)
to get simple patterns using the 'set' command ...

MTB Set c1
DATA 1( 1 : 72 / 1 )32
DATA End.

This generates (in column 1) 32 1's, then 32 2's, 32 3's ... down to 32 72's
very quickly and easily. I have not yet found anything similar in XL to do
this sort of thing but can't help the feling I'm missing something.

Anyone have any idea how to do this sort of thing in XL easily ?

Yes, I can do it in minitab and copy the column, but can't believe that XL
lacks what I think of as a basic function. What am I missing ? :)

Any help would be most welcome.

Thanks

It's really easy in minitab...
This generates (in column 1) 32 1's, then 32 2's, 32 3's ...
down to 32 72's


Hi. This is probably not the way most would write this.
Out of habit, I've adopted a poor-man's version of pure function
notation. (ie Row() can be threaded)


Sub MainProgram()
[A1].Resize(32 * 72) = MyPattern(32, 72)
End Sub


Function MyPattern(n, ul)
'// The Main Function:
Const Fx As String = "Transpose(Transpose(INT((ROW(#)+#)/#)))"

With [A1].Resize(n * ul)
MyPattern = Evaluate(Replace(Replace(Replace(Fx, "#",
..Address(False, False), , 1), "#", n - 1, , 1), "#", n, , 1))
End With
End Function

= = = = = = =
HTH :)
Dana DeLouis