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?

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?

Perfect and simple = "simply perfect"

Thanks cush

B.B.

"cush" wrote in message
...
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?

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" wrote in message
...
Perfect and simple = "simply perfect"

Thanks cush

B.B.

"cush" wrote in message
...
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?

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" wrote in message
...
Perfect and simple = "simply perfect"

Thanks cush

B.B.

"cush" wrote in message
...
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?

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" wrote in message
...
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" wrote in message
...
Perfect and simple = "simply perfect"

Thanks cush

B.B.

"cush" wrote in message
...
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?

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
'ToolsReferencesMicrosoft 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?

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" wrote in message
...
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" wrote in message
...
Perfect and simple = "simply perfect"

Thanks cush

B.B.

"cush" wrote in message
...
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?

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Perfect and simple = "simply perfect"

Thanks cush

B.B.

"cush" wrote in message
...
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,

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" wrote in message
...
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
'ToolsReferencesMicrosoft 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?

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" wrote in message
...
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
'ToolsReferencesMicrosoft 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?

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" wrote in message
...
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
'ToolsReferencesMicrosoft 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?

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

"cush" wrote in message
...
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" wrote in message
...
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
'ToolsReferencesMicrosoft 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?

All you need to do to use the code is:

'In the VBA Editor, click on
'ToolsReferencesMicrosoft Scripting RuntimeOK

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" wrote in message
...
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" wrote in message
...
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
'ToolsReferencesMicrosoft 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?