strange randomization

[William Benson]

User enters any array of numbers beginning in any cell: Example

A15 23
A16 11
A17 5
. .
. .
. .
A172 145
A173 220


Required: Formula (not VBA, but perhaps array-entered) which will:

1) return a RANDOM number from AMONG the items in the list
2) Not require that the list start, list end, or # of items in the
list be fixed (only col A is fixed).
3) not require that the list be in ascending or descending order
3) be entered in cell B1

I started something involving INDEX and the ROW() of the starting and ending
item, but I want it to be more dynamic. Then I tried to use MATCH to tell me
the start or end values, by using 0.001 and 1,000,000 and matching with
Match_Type = 1, or -1, but that required the items be in ascending or
descending order. So, I am stuck... can it be done?

[cush]

try this in B1:

=INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))

"William Benson" wrote:

> User enters any array of numbers beginning in any cell: Example
>
> A15 23
> A16 11
> A17 5
> . .
> . .
> . .
> A172 145
> A173 220
>
>
> Required: Formula (not VBA, but perhaps array-entered) which will:
>
> 1) return a RANDOM number from AMONG the items in the list
> 2) Not require that the list start, list end, or # of items in the
> list be fixed (only col A is fixed).
> 3) not require that the list be in ascending or descending order
> 3) be entered in cell B1
>
> I started something involving INDEX and the ROW() of the starting and ending
> item, but I want it to be more dynamic. Then I tried to use MATCH to tell me
> the start or end values, by using 0.001 and 1,000,000 and matching with
> Match_Type = 1, or -1, but that required the items be in ascending or
> descending order. So, I am stuck... can it be done?
>
>
>

[William Benson]

Perfect and simple = "simply perfect"

Thanks cush

B.B.

"cush" <[email protected]> wrote in message
news:[email protected]...
> try this in B1:
>
> =INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))
>
> "William Benson" wrote:
>
>> User enters any array of numbers beginning in any cell: Example
>>
>> A15 23
>> A16 11
>> A17 5
>> . .
>> . .
>> . .
>> A172 145
>> A173 220
>>
>>
>> Required: Formula (not VBA, but perhaps array-entered) which will:
>>
>> 1) return a RANDOM number from AMONG the items in the list
>> 2) Not require that the list start, list end, or # of items in
>> the
>> list be fixed (only col A is fixed).
>> 3) not require that the list be in ascending or descending order
>> 3) be entered in cell B1
>>
>> I started something involving INDEX and the ROW() of the starting and
>> ending
>> item, but I want it to be more dynamic. Then I tried to use MATCH to tell
>> me
>> the start or end values, by using 0.001 and 1,000,000 and matching with
>> Match_Type = 1, or -1, but that required the items be in ascending or
>> descending order. So, I am stuck... can it be done?
>>
>>
>>

[Tom Ogilvy]

An excellent solution - but
Just a heads up, that you must start your numbers in A1 with this solution.
That was not a requirement in your specification - reason for the warning.

--
Regards,
Tom Ogilvy

"William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> Perfect and simple = "simply perfect"
>
> Thanks cush
>
> B.B.
>
> "cush" <[email protected]> wrote in message
> news:[email protected]...
> > try this in B1:
> >
> > =INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))
> >
> > "William Benson" wrote:
> >
> >> User enters any array of numbers beginning in any cell: Example
> >>
> >> A15 23
> >> A16 11
> >> A17 5
> >> . .
> >> . .
> >> . .
> >> A172 145
> >> A173 220
> >>
> >>
> >> Required: Formula (not VBA, but perhaps array-entered) which will:
> >>
> >> 1) return a RANDOM number from AMONG the items in the list
> >> 2) Not require that the list start, list end, or # of items in
> >> the
> >> list be fixed (only col A is fixed).
> >> 3) not require that the list be in ascending or descending
order
> >> 3) be entered in cell B1
> >>
> >> I started something involving INDEX and the ROW() of the starting and
> >> ending
> >> item, but I want it to be more dynamic. Then I tried to use MATCH to
tell
> >> me
> >> the start or end values, by using 0.001 and 1,000,000 and matching with
> >> Match_Type = 1, or -1, but that required the items be in ascending or
> >> descending order. So, I am stuck... can it be done?
> >>
> >>
> >>
>
>

[cush]

Actually, the numbers do not have to start in A1. You can have blanks, but
not text,(labels, or headers).

"Tom Ogilvy" wrote:

> An excellent solution - but
> Just a heads up, that you must start your numbers in A1 with this solution.
> That was not a requirement in your specification - reason for the warning.
>
> --
> Regards,
> Tom Ogilvy
>
> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
> > Perfect and simple = "simply perfect"
> >
> > Thanks cush
> >
> > B.B.
> >
> > "cush" <[email protected]> wrote in message
> > news:[email protected]...
> > > try this in B1:
> > >
> > > =INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))
> > >
> > > "William Benson" wrote:
> > >
> > >> User enters any array of numbers beginning in any cell: Example
> > >>
> > >> A15 23
> > >> A16 11
> > >> A17 5
> > >> . .
> > >> . .
> > >> . .
> > >> A172 145
> > >> A173 220
> > >>
> > >>
> > >> Required: Formula (not VBA, but perhaps array-entered) which will:
> > >>
> > >> 1) return a RANDOM number from AMONG the items in the list
> > >> 2) Not require that the list start, list end, or # of items in
> > >> the
> > >> list be fixed (only col A is fixed).
> > >> 3) not require that the list be in ascending or descending
> order
> > >> 3) be entered in cell B1
> > >>
> > >> I started something involving INDEX and the ROW() of the starting and
> > >> ending
> > >> item, but I want it to be more dynamic. Then I tried to use MATCH to
> tell
> > >> me
> > >> the start or end values, by using 0.001 and 1,000,000 and matching with
> > >> Match_Type = 1, or -1, but that required the items be in ascending or
> > >> descending order. So, I am stuck... can it be done?
> > >>
> > >>
> > >>
> >
> >
>
>
>

[Tom Ogilvy]

If I put 3 numbers at row 100, the counta would be 3*rand(). leading to
integers 0,1,2 as offsets to A1. To me that says it doesn't work, but
maybe I don't understand the problem.

--
Regards,
Tom Ogilvy

"cush" <[email protected]> wrote in message
news:[email protected]...
> Actually, the numbers do not have to start in A1. You can have blanks,
but
> not text,(labels, or headers).
>
> "Tom Ogilvy" wrote:
>
> > An excellent solution - but
> > Just a heads up, that you must start your numbers in A1 with this
solution.
> > That was not a requirement in your specification - reason for the
warning.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> > news:[email protected]...
> > > Perfect and simple = "simply perfect"
> > >
> > > Thanks cush
> > >
> > > B.B.
> > >
> > > "cush" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > try this in B1:
> > > >
> > > > =INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))
> > > >
> > > > "William Benson" wrote:
> > > >
> > > >> User enters any array of numbers beginning in any cell: Example
> > > >>
> > > >> A15 23
> > > >> A16 11
> > > >> A17 5
> > > >> . .
> > > >> . .
> > > >> . .
> > > >> A172 145
> > > >> A173 220
> > > >>
> > > >>
> > > >> Required: Formula (not VBA, but perhaps array-entered) which will:
> > > >>
> > > >> 1) return a RANDOM number from AMONG the items in the list
> > > >> 2) Not require that the list start, list end, or # of items
in
> > > >> the
> > > >> list be fixed (only col A is fixed).
> > > >> 3) not require that the list be in ascending or descending
> > order
> > > >> 3) be entered in cell B1
> > > >>
> > > >> I started something involving INDEX and the ROW() of the starting
and
> > > >> ending
> > > >> item, but I want it to be more dynamic. Then I tried to use MATCH
to
> > tell
> > > >> me
> > > >> the start or end values, by using 0.001 and 1,000,000 and matching
with
> > > >> Match_Type = 1, or -1, but that required the items be in ascending
or
> > > >> descending order. So, I am stuck... can it be done?
> > > >>
> > > >>
> > > >>
> > >
> > >
> >
> >
> >

[cush]

If I were doing this I would first insert a col B (later to be hidden) where
I would consolidate all the data from colA (getting rid of blanks and
duplicates) with the following macro:

Sub CreateUniqueList
'Requires a reference to MS Scripting Runtime
'In the VBA Editor, click on
'Tools>References>Microsoft Scripting Runtime
''''''''''''''''''''''''''''''''''''''''''''''

Dim Dict As Scripting.Dictionary
Dim i As Integer
Dim oCell As Range
Dim Source As Range
Dim Uniques As Range

Set Source = Range("A:A")
Set Uniques = Range("B:B")

Set Dict = New Dictionary
i = 1
With Dict
For Each oCell In Source
If Not .Exists(oCell.Value) Then
If Not oCell.Value = "" Then
.Add Key:=oCell.Value, Item:=i
Uniques(i) = oCell.Value
i = i + 1

End If
End If
Next oCell
End With

Shutdown:
Set Dict = Nothing

End Sub

I would then change my original formula from A:A to B:B

=INDEX(B:B,ROUND(RAND()*COUNTA(B:B),0))

This would clean it up and avoid problems with blanks.

"William Benson" wrote:

> User enters any array of numbers beginning in any cell: Example
>
> A15 23
> A16 11
> A17 5
> . .
> . .
> . .
> A172 145
> A173 220
>
>
> Required: Formula (not VBA, but perhaps array-entered) which will:
>
> 1) return a RANDOM number from AMONG the items in the list
> 2) Not require that the list start, list end, or # of items in the
> list be fixed (only col A is fixed).
> 3) not require that the list be in ascending or descending order
> 3) be entered in cell B1
>
> I started something involving INDEX and the ROW() of the starting and ending
> item, but I want it to be more dynamic. Then I tried to use MATCH to tell me
> the start or end values, by using 0.001 and 1,000,000 and matching with
> Match_Type = 1, or -1, but that required the items be in ascending or
> descending order. So, I am stuck... can it be done?
>
>
>

[William Benson]

I think it is all about the INDEX function ... the number of places deep in
the column will be a function of the CountA result.

Suppose there are 5 items in the list but I begin in Row 19

the COUNTA will tell be to dive no more than 5 into the column's depth, but
my data doesn't even start until row 19 ... so I will always get zero.

That's what Tom means, I think. I am presently looking for a workaround :-)

Bill
"cush" <[email protected]> wrote in message
news:[email protected]...
> Actually, the numbers do not have to start in A1. You can have blanks,
> but
> not text,(labels, or headers).
>
> "Tom Ogilvy" wrote:
>
>> An excellent solution - but
>> Just a heads up, that you must start your numbers in A1 with this
>> solution.
>> That was not a requirement in your specification - reason for the
>> warning.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> news:[email protected]...
>> > Perfect and simple = "simply perfect"
>> >
>> > Thanks cush
>> >
>> > B.B.
>> >
>> > "cush" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > try this in B1:
>> > >
>> > > =INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))
>> > >
>> > > "William Benson" wrote:
>> > >
>> > >> User enters any array of numbers beginning in any cell: Example
>> > >>
>> > >> A15 23
>> > >> A16 11
>> > >> A17 5
>> > >> . .
>> > >> . .
>> > >> . .
>> > >> A172 145
>> > >> A173 220
>> > >>
>> > >>
>> > >> Required: Formula (not VBA, but perhaps array-entered) which will:
>> > >>
>> > >> 1) return a RANDOM number from AMONG the items in the list
>> > >> 2) Not require that the list start, list end, or # of items
>> > >> in
>> > >> the
>> > >> list be fixed (only col A is fixed).
>> > >> 3) not require that the list be in ascending or descending
>> order
>> > >> 3) be entered in cell B1
>> > >>
>> > >> I started something involving INDEX and the ROW() of the starting
>> > >> and
>> > >> ending
>> > >> item, but I want it to be more dynamic. Then I tried to use MATCH to
>> tell
>> > >> me
>> > >> the start or end values, by using 0.001 and 1,000,000 and matching
>> > >> with
>> > >> Match_Type = 1, or -1, but that required the items be in ascending
>> > >> or
>> > >> descending order. So, I am stuck... can it be done?
>> > >>
>> > >>
>> > >>
>> >
>> >
>>
>>
>>

[William Benson]

Not PERFECT, but here is as near to perfect as I can get:

Wherever you are going to start entering values, begin by entering
characters, not a number, then numbers from then on.

Then, the formula I got is:

=INDEX(A:A,INT((COUNTA(A:A)-1)*RAND()+(MATCH("*",A:A,0)+1)))


"William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
>I think it is all about the INDEX function ... the number of places deep in
>the column will be a function of the CountA result.
>
> Suppose there are 5 items in the list but I begin in Row 19
>
> the COUNTA will tell be to dive no more than 5 into the column's depth,
> but my data doesn't even start until row 19 ... so I will always get zero.
>
> That's what Tom means, I think. I am presently looking for a workaround
> :-)
>
> Bill
> "cush" <[email protected]> wrote in message
> news:[email protected]...
>> Actually, the numbers do not have to start in A1. You can have blanks,
>> but
>> not text,(labels, or headers).
>>
>> "Tom Ogilvy" wrote:
>>
>>> An excellent solution - but
>>> Just a heads up, that you must start your numbers in A1 with this
>>> solution.
>>> That was not a requirement in your specification - reason for the
>>> warning.
>>>
>>> --
>>> Regards,
>>> Tom Ogilvy
>>>
>>> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>>> news:[email protected]...
>>> > Perfect and simple = "simply perfect"
>>> >
>>> > Thanks cush
>>> >
>>> > B.B.
>>> >
>>> > "cush" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> > > try this in B1:
>>> > >
>>> > > =INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))
>>> > >
>>> > > "William Benson" wrote:
>>> > >
>>> > >> User enters any array of numbers beginning in any cell: Example
>>> > >>
>>> > >> A15 23
>>> > >> A16 11
>>> > >> A17 5
>>> > >> . .
>>> > >> . .
>>> > >> . .
>>> > >> A172 145
>>> > >> A173 220
>>> > >>
>>> > >>
>>> > >> Required: Formula (not VBA, but perhaps array-entered) which will:
>>> > >>
>>> > >> 1) return a RANDOM number from AMONG the items in the list
>>> > >> 2) Not require that the list start, list end, or # of items
>>> > >> in
>>> > >> the
>>> > >> list be fixed (only col A is fixed).
>>> > >> 3) not require that the list be in ascending or descending
>>> order
>>> > >> 3) be entered in cell B1
>>> > >>
>>> > >> I started something involving INDEX and the ROW() of the starting
>>> > >> and
>>> > >> ending
>>> > >> item, but I want it to be more dynamic. Then I tried to use MATCH
>>> > >> to
>>> tell
>>> > >> me
>>> > >> the start or end values, by using 0.001 and 1,000,000 and matching
>>> > >> with
>>> > >> Match_Type = 1, or -1, but that required the items be in ascending
>>> > >> or
>>> > >> descending order. So, I am stuck... can it be done?
>>> > >>
>>> > >>
>>> > >>
>>> >
>>> >
>>>
>>>
>>>
>
>

[William Benson]

Cush,

I haven't heard of dictionary, can you tell me how dictionary differs from
collection different?

This line: Uniques(i) = oCell.Value

is going to mean a value gets entered in the uniques column whether or not
it had occurred prior, right?

Sorry, it is late for me, I may not be thinking right.
"cush" <[email protected]> wrote in message
news:[email protected]...
> If I were doing this I would first insert a col B (later to be hidden)
> where
> I would consolidate all the data from colA (getting rid of blanks and
> duplicates) with the following macro:
>
> Sub CreateUniqueList
> 'Requires a reference to MS Scripting Runtime
> 'In the VBA Editor, click on
> 'Tools>References>Microsoft Scripting Runtime
> ''''''''''''''''''''''''''''''''''''''''''''''
>
> Dim Dict As Scripting.Dictionary
> Dim i As Integer
> Dim oCell As Range
> Dim Source As Range
> Dim Uniques As Range
>
> Set Source = Range("A:A")
> Set Uniques = Range("B:B")
>
> Set Dict = New Dictionary
> i = 1
> With Dict
> For Each oCell In Source
> If Not .Exists(oCell.Value) Then
> If Not oCell.Value = "" Then
> .Add Key:=oCell.Value, Item:=i
> Uniques(i) = oCell.Value
> i = i + 1
>
> End If
> End If
> Next oCell
> End With
>
> Shutdown:
> Set Dict = Nothing
>
> End Sub
>
> I would then change my original formula from A:A to B:B
>
> =INDEX(B:B,ROUND(RAND()*COUNTA(B:B),0))
>
> This would clean it up and avoid problems with blanks.
>
> "William Benson" wrote:
>
>> User enters any array of numbers beginning in any cell: Example
>>
>> A15 23
>> A16 11
>> A17 5
>> . .
>> . .
>> . .
>> A172 145
>> A173 220
>>
>>
>> Required: Formula (not VBA, but perhaps array-entered) which will:
>>
>> 1) return a RANDOM number from AMONG the items in the list
>> 2) Not require that the list start, list end, or # of items in
>> the
>> list be fixed (only col A is fixed).
>> 3) not require that the list be in ascending or descending order
>> 3) be entered in cell B1
>>
>> I started something involving INDEX and the ROW() of the starting and
>> ending
>> item, but I want it to be more dynamic. Then I tried to use MATCH to tell
>> me
>> the start or end values, by using 0.001 and 1,000,000 and matching with
>> Match_Type = 1, or -1, but that required the items be in ascending or
>> descending order. So, I am stuck... can it be done?
>>
>>
>>

[cush]

No, only unique items are added to the list in Col B:

If Not .Exists(oCell.Value) Then
If Not oCell.Value = "" Then
.Add Key:=oCell.Value, Item:=i
Uniques(i) = oCell.Value

Dictionary is similar to Collection
It has a property called .Exists() which looks to see if the item already
exists
in the list. Line 1 above (from my code) says that if the value
does not exist in the Dictionary list, then (from line 3) add it to the list.
I also got rid of blanks with line 2 above.
In addition to adding items to the dictionary, I added items
to the Range in Col B with line 4 above.

This abreviated list in Col B (Uniques) now contains no blanks and no
duplicates.
Perhaps you want duplicates if they happen to exist in Col A.
If so, remove line 2 above (plus the accompanying End If).
This will statistically change the number of times a duplicated
item appears in your random cell (C1). --- Your choice.

Hope this helps


"William Benson" wrote:

> Cush,
>
> I haven't heard of dictionary, can you tell me how dictionary differs from
> collection different?
>
> This line: Uniques(i) = oCell.Value
>
> is going to mean a value gets entered in the uniques column whether or not
> it had occurred prior, right?
>
> Sorry, it is late for me, I may not be thinking right.
> "cush" <[email protected]> wrote in message
> news:[email protected]...
> > If I were doing this I would first insert a col B (later to be hidden)
> > where
> > I would consolidate all the data from colA (getting rid of blanks and
> > duplicates) with the following macro:
> >
> > Sub CreateUniqueList
> > 'Requires a reference to MS Scripting Runtime
> > 'In the VBA Editor, click on
> > 'Tools>References>Microsoft Scripting Runtime
> > ''''''''''''''''''''''''''''''''''''''''''''''
> >
> > Dim Dict As Scripting.Dictionary
> > Dim i As Integer
> > Dim oCell As Range
> > Dim Source As Range
> > Dim Uniques As Range
> >
> > Set Source = Range("A:A")
> > Set Uniques = Range("B:B")
> >
> > Set Dict = New Dictionary
> > i = 1
> > With Dict
> > For Each oCell In Source
> > If Not .Exists(oCell.Value) Then
> > If Not oCell.Value = "" Then
> > .Add Key:=oCell.Value, Item:=i
> > Uniques(i) = oCell.Value
> > i = i + 1
> >
> > End If
> > End If
> > Next oCell
> > End With
> >
> > Shutdown:
> > Set Dict = Nothing
> >
> > End Sub
> >
> > I would then change my original formula from A:A to B:B
> >
> > =INDEX(B:B,ROUND(RAND()*COUNTA(B:B),0))
> >
> > This would clean it up and avoid problems with blanks.
> >
> > "William Benson" wrote:
> >
> >> User enters any array of numbers beginning in any cell: Example
> >>
> >> A15 23
> >> A16 11
> >> A17 5
> >> . .
> >> . .
> >> . .
> >> A172 145
> >> A173 220
> >>
> >>
> >> Required: Formula (not VBA, but perhaps array-entered) which will:
> >>
> >> 1) return a RANDOM number from AMONG the items in the list
> >> 2) Not require that the list start, list end, or # of items in
> >> the
> >> list be fixed (only col A is fixed).
> >> 3) not require that the list be in ascending or descending order
> >> 3) be entered in cell B1
> >>
> >> I started something involving INDEX and the ROW() of the starting and
> >> ending
> >> item, but I want it to be more dynamic. Then I tried to use MATCH to tell
> >> me
> >> the start or end values, by using 0.001 and 1,000,000 and matching with
> >> Match_Type = 1, or -1, but that required the items be in ascending or
> >> descending order. So, I am stuck... can it be done?
> >>
> >>
> >>
>
>
>

[cush]

Further:
I am not totally sure of this but I think the Collection object is part of
Excel's object model, whereas Dictionary is not and requires a reference to
MS Scripting Runtime (a dll) if you want to write code using this object.
The end user is NOT required to have such a reference.

Also, the properties and methods are a little different.
Google on Dictionary for more info.

"William Benson" wrote:

> Cush,
>
> I haven't heard of dictionary, can you tell me how dictionary differs from
> collection different?
>
> This line: Uniques(i) = oCell.Value
>
> is going to mean a value gets entered in the uniques column whether or not
> it had occurred prior, right?
>
> Sorry, it is late for me, I may not be thinking right.
> "cush" <[email protected]> wrote in message
> news:[email protected]...
> > If I were doing this I would first insert a col B (later to be hidden)
> > where
> > I would consolidate all the data from colA (getting rid of blanks and
> > duplicates) with the following macro:
> >
> > Sub CreateUniqueList
> > 'Requires a reference to MS Scripting Runtime
> > 'In the VBA Editor, click on
> > 'Tools>References>Microsoft Scripting Runtime
> > ''''''''''''''''''''''''''''''''''''''''''''''
> >
> > Dim Dict As Scripting.Dictionary
> > Dim i As Integer
> > Dim oCell As Range
> > Dim Source As Range
> > Dim Uniques As Range
> >
> > Set Source = Range("A:A")
> > Set Uniques = Range("B:B")
> >
> > Set Dict = New Dictionary
> > i = 1
> > With Dict
> > For Each oCell In Source
> > If Not .Exists(oCell.Value) Then
> > If Not oCell.Value = "" Then
> > .Add Key:=oCell.Value, Item:=i
> > Uniques(i) = oCell.Value
> > i = i + 1
> >
> > End If
> > End If
> > Next oCell
> > End With
> >
> > Shutdown:
> > Set Dict = Nothing
> >
> > End Sub
> >
> > I would then change my original formula from A:A to B:B
> >
> > =INDEX(B:B,ROUND(RAND()*COUNTA(B:B),0))
> >
> > This would clean it up and avoid problems with blanks.
> >
> > "William Benson" wrote:
> >
> >> User enters any array of numbers beginning in any cell: Example
> >>
> >> A15 23
> >> A16 11
> >> A17 5
> >> . .
> >> . .
> >> . .
> >> A172 145
> >> A173 220
> >>
> >>
> >> Required: Formula (not VBA, but perhaps array-entered) which will:
> >>
> >> 1) return a RANDOM number from AMONG the items in the list
> >> 2) Not require that the list start, list end, or # of items in
> >> the
> >> list be fixed (only col A is fixed).
> >> 3) not require that the list be in ascending or descending order
> >> 3) be entered in cell B1
> >>
> >> I started something involving INDEX and the ROW() of the starting and
> >> ending
> >> item, but I want it to be more dynamic. Then I tried to use MATCH to tell
> >> me
> >> the start or end values, by using 0.001 and 1,000,000 and matching with
> >> Match_Type = 1, or -1, but that required the items be in ascending or
> >> descending order. So, I am stuck... can it be done?
> >>
> >>
> >>
>
>
>

[William Benson]

Thanks, will look it up and see if there is a way I can use it.

"cush" <[email protected]> wrote in message
news:[email protected]...
> Further:
> I am not totally sure of this but I think the Collection object is part of
> Excel's object model, whereas Dictionary is not and requires a reference
> to
> MS Scripting Runtime (a dll) if you want to write code using this object.
> The end user is NOT required to have such a reference.
>
> Also, the properties and methods are a little different.
> Google on Dictionary for more info.
>
> "William Benson" wrote:
>
>> Cush,
>>
>> I haven't heard of dictionary, can you tell me how dictionary differs
>> from
>> collection different?
>>
>> This line: Uniques(i) = oCell.Value
>>
>> is going to mean a value gets entered in the uniques column whether or
>> not
>> it had occurred prior, right?
>>
>> Sorry, it is late for me, I may not be thinking right.
>> "cush" <[email protected]> wrote in message
>> news:[email protected]...
>> > If I were doing this I would first insert a col B (later to be hidden)
>> > where
>> > I would consolidate all the data from colA (getting rid of blanks and
>> > duplicates) with the following macro:
>> >
>> > Sub CreateUniqueList
>> > 'Requires a reference to MS Scripting Runtime
>> > 'In the VBA Editor, click on
>> > 'Tools>References>Microsoft Scripting Runtime
>> > ''''''''''''''''''''''''''''''''''''''''''''''
>> >
>> > Dim Dict As Scripting.Dictionary
>> > Dim i As Integer
>> > Dim oCell As Range
>> > Dim Source As Range
>> > Dim Uniques As Range
>> >
>> > Set Source = Range("A:A")
>> > Set Uniques = Range("B:B")
>> >
>> > Set Dict = New Dictionary
>> > i = 1
>> > With Dict
>> > For Each oCell In Source
>> > If Not .Exists(oCell.Value) Then
>> > If Not oCell.Value = "" Then
>> > .Add Key:=oCell.Value, Item:=i
>> > Uniques(i) = oCell.Value
>> > i = i + 1
>> >
>> > End If
>> > End If
>> > Next oCell
>> > End With
>> >
>> > Shutdown:
>> > Set Dict = Nothing
>> >
>> > End Sub
>> >
>> > I would then change my original formula from A:A to B:B
>> >
>> > =INDEX(B:B,ROUND(RAND()*COUNTA(B:B),0))
>> >
>> > This would clean it up and avoid problems with blanks.
>> >
>> > "William Benson" wrote:
>> >
>> >> User enters any array of numbers beginning in any cell: Example
>> >>
>> >> A15 23
>> >> A16 11
>> >> A17 5
>> >> . .
>> >> . .
>> >> . .
>> >> A172 145
>> >> A173 220
>> >>
>> >>
>> >> Required: Formula (not VBA, but perhaps array-entered) which will:
>> >>
>> >> 1) return a RANDOM number from AMONG the items in the list
>> >> 2) Not require that the list start, list end, or # of items in
>> >> the
>> >> list be fixed (only col A is fixed).
>> >> 3) not require that the list be in ascending or descending
>> >> order
>> >> 3) be entered in cell B1
>> >>
>> >> I started something involving INDEX and the ROW() of the starting and
>> >> ending
>> >> item, but I want it to be more dynamic. Then I tried to use MATCH to
>> >> tell
>> >> me
>> >> the start or end values, by using 0.001 and 1,000,000 and matching
>> >> with
>> >> Match_Type = 1, or -1, but that required the items be in ascending or
>> >> descending order. So, I am stuck... can it be done?
>> >>
>> >>
>> >>
>>
>>
>>

[cush]

All you need to do to use the code is:

'In the VBA Editor, click on
'Tools>References>Microsoft Scripting Runtime>OK

then copy and paste my code into a standard module


"William Benson" wrote:

> Thanks, will look it up and see if there is a way I can use it.
>
> "cush" <[email protected]> wrote in message
> news:[email protected]...
> > Further:
> > I am not totally sure of this but I think the Collection object is part of
> > Excel's object model, whereas Dictionary is not and requires a reference
> > to
> > MS Scripting Runtime (a dll) if you want to write code using this object.
> > The end user is NOT required to have such a reference.
> >
> > Also, the properties and methods are a little different.
> > Google on Dictionary for more info.
> >
> > "William Benson" wrote:
> >
> >> Cush,
> >>
> >> I haven't heard of dictionary, can you tell me how dictionary differs
> >> from
> >> collection different?
> >>
> >> This line: Uniques(i) = oCell.Value
> >>
> >> is going to mean a value gets entered in the uniques column whether or
> >> not
> >> it had occurred prior, right?
> >>
> >> Sorry, it is late for me, I may not be thinking right.
> >> "cush" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > If I were doing this I would first insert a col B (later to be hidden)
> >> > where
> >> > I would consolidate all the data from colA (getting rid of blanks and
> >> > duplicates) with the following macro:
> >> >
> >> > Sub CreateUniqueList
> >> > 'Requires a reference to MS Scripting Runtime
> >> > 'In the VBA Editor, click on
> >> > 'Tools>References>Microsoft Scripting Runtime
> >> > ''''''''''''''''''''''''''''''''''''''''''''''
> >> >
> >> > Dim Dict As Scripting.Dictionary
> >> > Dim i As Integer
> >> > Dim oCell As Range
> >> > Dim Source As Range
> >> > Dim Uniques As Range
> >> >
> >> > Set Source = Range("A:A")
> >> > Set Uniques = Range("B:B")
> >> >
> >> > Set Dict = New Dictionary
> >> > i = 1
> >> > With Dict
> >> > For Each oCell In Source
> >> > If Not .Exists(oCell.Value) Then
> >> > If Not oCell.Value = "" Then
> >> > .Add Key:=oCell.Value, Item:=i
> >> > Uniques(i) = oCell.Value
> >> > i = i + 1
> >> >
> >> > End If
> >> > End If
> >> > Next oCell
> >> > End With
> >> >
> >> > Shutdown:
> >> > Set Dict = Nothing
> >> >
> >> > End Sub
> >> >
> >> > I would then change my original formula from A:A to B:B
> >> >
> >> > =INDEX(B:B,ROUND(RAND()*COUNTA(B:B),0))
> >> >
> >> > This would clean it up and avoid problems with blanks.
> >> >
> >> > "William Benson" wrote:
> >> >
> >> >> User enters any array of numbers beginning in any cell: Example
> >> >>
> >> >> A15 23
> >> >> A16 11
> >> >> A17 5
> >> >> . .
> >> >> . .
> >> >> . .
> >> >> A172 145
> >> >> A173 220
> >> >>
> >> >>
> >> >> Required: Formula (not VBA, but perhaps array-entered) which will:
> >> >>
> >> >> 1) return a RANDOM number from AMONG the items in the list
> >> >> 2) Not require that the list start, list end, or # of items in
> >> >> the
> >> >> list be fixed (only col A is fixed).
> >> >> 3) not require that the list be in ascending or descending
> >> >> order
> >> >> 3) be entered in cell B1
> >> >>
> >> >> I started something involving INDEX and the ROW() of the starting and
> >> >> ending
> >> >> item, but I want it to be more dynamic. Then I tried to use MATCH to
> >> >> tell
> >> >> me
> >> >> the start or end values, by using 0.001 and 1,000,000 and matching
> >> >> with
> >> >> Match_Type = 1, or -1, but that required the items be in ascending or
> >> >> descending order. So, I am stuck... can it be done?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>