In a two dimensional array I want to find the position of
the minimum value in the first dimension and return the
value from the equivalent position in the second dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons do
not want to sort. If necessary my two dim array could be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul

Hi Paul,

If you already have the data in a VBA array I think the fastest solution
would be a single pass loop: any other solution is going to have to read
each element at least once anyway.
I suspect that the additional overhead of using a worksheet function will
outweigh any speed gain you might get.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Paul Stevens" wrote in message
...
In a two dimensional array I want to find the position of
the minimum value in the first dimension and return the
value from the equivalent position in the second dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons do
not want to sort. If necessary my two dim array could be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul

Hi Charles,

Thanks for stopping me going down a blind alley.
Following your advice, to get value in 2nd dim from
minimum in 1st dim I'm now doing this

MinVal = 1 'known maximum possible value
For i = 0 To UBound(myArray)
If MinVal myArray(i, 0) Then
MinVal = myArray(i, 0)
MinIndex = i
End If
Next
Result = myArray(MinIndex, 1)

Unfortunately I need to return values in the 2nd dim'
corresponding with the 10 smallest values in the 1st dim'.
Looping with .Small seemed ideal as a starter but no
direct way to get offset values without looping the entire
array. Didn't mention this earlier as I thought possible
without looping and the principle would be the same.
Never mind, I'll work with your suggested approach.

Thanks again,
Paul

-----Original Message-----
Hi Paul,

If you already have the data in a VBA array I think the
fastest solution
would be a single pass loop: any other solution is going
to have to read
each element at least once anyway.
I suspect that the additional overhead of using a
worksheet function will
outweigh any speed gain you might get.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the position
of
the minimum value in the first dimension and return the
value from the equivalent position in the second
dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons
do
not want to sort. If necessary my two dim array could
be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul


.

Did you say "Looping?" ;) <vbg

Don't know if this is what you want. In a 2-dim array, this should return
an array of the 10 smallest numbers in the first column.

Sub Demo()
'// Dana DeLouis
Dim v, TenSmallest
v = [A1:B20]
With WorksheetFunction
ActiveWorkbook.Names.Add "v_", .Transpose(.Index(v, 0, 1))
End With
TenSmallest = [TRANSPOSE(TRANSPOSE(SMALL(v_,{1,2,3,4,5,6,7,8,9,10 })))]
ActiveWorkbook.Names("v_").Delete
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens" wrote in message
...
Hi Charles,

Thanks for stopping me going down a blind alley.
Following your advice, to get value in 2nd dim from
minimum in 1st dim I'm now doing this

MinVal = 1 'known maximum possible value
For i = 0 To UBound(myArray)
If MinVal myArray(i, 0) Then
MinVal = myArray(i, 0)
MinIndex = i
End If
Next
Result = myArray(MinIndex, 1)

Unfortunately I need to return values in the 2nd dim'
corresponding with the 10 smallest values in the 1st dim'.
Looping with .Small seemed ideal as a starter but no
direct way to get offset values without looping the entire
array. Didn't mention this earlier as I thought possible
without looping and the principle would be the same.
Never mind, I'll work with your suggested approach.

Thanks again,
Paul

-----Original Message-----
Hi Paul,

If you already have the data in a VBA array I think the
fastest solution
would be a single pass loop: any other solution is going
to have to read
each element at least once anyway.
I suspect that the additional overhead of using a
worksheet function will
outweigh any speed gain you might get.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the position
of
the minimum value in the first dimension and return the
value from the equivalent position in the second
dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons
do
not want to sort. If necessary my two dim array could
be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul


.

Hi Dana,

Wow! I would never have thought of that!!

This appears to extract the 10 smallest values in order
from the 1st column of v(), rather than the corresponding
values from the second as I need (but I think solved with
your first suggestion as adapted).

However I already have another good use for this as is.

Thanks again,
Paul

PS Your 2nd message (ie this) came in subsequent to my
reply to your first, despite posting times indicating
otherwise.

-----Original Message-----
Did you say "Looping?" ;) <vbg

Don't know if this is what you want. In a 2-dim array,
this should return
an array of the 10 smallest numbers in the first column.

Sub Demo()
'// Dana DeLouis
Dim v, TenSmallest
v = [A1:B20]
With WorksheetFunction
ActiveWorkbook.Names.Add "v_", .Transpose(.Index
(v, 0, 1))
End With
TenSmallest = [TRANSPOSE(TRANSPOSE(SMALL(v_,
{1,2,3,4,5,6,7,8,9,10})))]
ActiveWorkbook.Names("v_").Delete
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"
wrote in message
...
Hi Charles,

Thanks for stopping me going down a blind alley.
Following your advice, to get value in 2nd dim from
minimum in 1st dim I'm now doing this

MinVal = 1 'known maximum possible value
For i = 0 To UBound(myArray)
If MinVal myArray(i, 0) Then
MinVal = myArray(i, 0)
MinIndex = i
End If
Next
Result = myArray(MinIndex, 1)

Unfortunately I need to return values in the 2nd dim'
corresponding with the 10 smallest values in the 1st
dim'.
Looping with .Small seemed ideal as a starter but no
direct way to get offset values without looping the
entire
array. Didn't mention this earlier as I thought possible
without looping and the principle would be the same.
Never mind, I'll work with your suggested approach.

Thanks again,
Paul

-----Original Message-----
Hi Paul,

If you already have the data in a VBA array I think the
fastest solution
would be a single pass loop: any other solution is
going
to have to read
each element at least once anyway.
I suspect that the additional overhead of using a
worksheet function will
outweigh any speed gain you might get.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the
position
of
the minimum value in the first dimension and return
the
value from the equivalent position in the second
dimension.

My array is a VBA variable, not a range reference,
and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other
reasons
do
not want to sort. If necessary my two dim array
could
be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul


.



.

Hi Paul & Dana,

Dana's solution is very elegant but looping seems to be much faster on my
system using Excel97 :

Demo3 takes about 48 milliseconds on my system for 2500 rows.

The looping solution below takes about 1.6 millisecs for 2500 rows (and does
not have the 5468 array function limit), and its pretty much linear with the
number of rows (10000 rows takes about 5.6 millisecs)

Option Explicit
Option Base 1
Private Declare Function getFrequency Lib "kernel32" Alias
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
Public Function MicroTimer() As Double
Dim cyTicks1 As Currency
Static cyFrequency As Currency
10 MicroTimer = 0
20 If cyFrequency = 0 Then getFrequency cyFrequency
30 getTickCount cyTicks1
40 If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function

Sub FindSmallest()
Dim vArr As Variant
Dim iLargeIX As Long
Dim dSmallNums() As Double
Dim iSmallIX() As Long
Dim j As Long
Dim dAnsa() As Double
Dim dtime As Double
Dim nSmalls As Long
Dim nRows As Long

nSmalls = 10
nRows = 2500

vArr = Worksheets("Sheet1").Range("a1").Resize(nRows, 2)
ReDim dSmallNums(nSmalls) As Double
ReDim iSmallIX(nSmalls) As Long
ReDim dAnsa(nSmalls) As Double
dtime = MicroTimer()
For j = 1 To nSmalls
dSmallNums(j) = vArr(j, 1)
iSmallIX(j) = j
Next j

iLargeIX = FindLargest(dSmallNums)

For j = nSmalls + 1 To nRows
If vArr(j, 1) < dSmallNums(iLargeIX) Then
dSmallNums(iLargeIX) = vArr(j, 1)
iSmallIX(iLargeIX) = j
iLargeIX = FindLargest(dSmallNums)
End If
Next j

For j = 1 To nSmalls
dAnsa(j) = vArr(iSmallIX(j), 2)
Next j

dtime = MicroTimer() - dtime
MsgBox dtime * 1000 & " Millisecs"
End Sub

Function FindLargest(dSmallNums() As Double) As Long
Dim j As Long
Dim dLarge As Double
dLarge = dSmallNums(1)
FindLargest = 1
For j = 2 To UBound(dSmallNums)
If dSmallNums(j) dLarge Then
FindLargest = j
dLarge = dSmallNums(j)
End If
Next j
End Function

Sub Demo3()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight
Dim Column1
Dim i As Long
Dim dtime As Double

v = [A1:B2500]

dtime = MicroTimer()
With WorksheetFunction
'Keep next line out of loop...
Column1 = .Index(v, 0, 1)
For i = 1 To 10
MinLeft = .Small(Column1, i)
ValueRight = .Index(v, .Match(MinLeft, Column1, 0), 2)
'Cells(i, 3).Resize(1, 2) = Array(MinLeft, ValueRight)
Next i
End With
dtime = MicroTimer() - dtime
MsgBox dtime * 1000 & " Millisecs"
End Sub

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

I'm not sure, but would any ideas here help you. I made a 2-dim array
quickly from a worksheet. There might be a faster way, but unsorted data
makes it a little harder.

Sub Demo()
'// Dana DeLouis
Dim v, MinLeft, ValueRight
v = [A1:B20]

With WorksheetFunction
MinLeft = .Min(.Index(v, 0, 1))
ValueRight = .Index(v, .Match(MinLeft, .Index(v, 0, 1), 0), 2)
End With
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens" wrote in message
...
In a two dimensional array I want to find the position of
the minimum value in the first dimension and return the
value from the equivalent position in the second dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons do
not want to sort. If necessary my two dim array could be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul

Dana,

Very interesting, you've come up with what I had been
struggling to find.

In my reply to Charles I mentioned my additional "smallest
10 values problem", I've adapted your code:

Sub Demo2()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight, i
v = [A1:B20] 'populated with =RAND(), then
'pastespecial/values back
For i = 1 To 10
With WorksheetFunction
MinLeft = .Small(.Index(v, 0, 1), i)
ValueRight = .Index(v, .Match(MinLeft, _
.Index(v, 0, 1), 0), 2)
End With

Cells(i, 3) = MinLeft
Cells(i, 4) = ValueRight
Next
'sort A1:B20 with colA and compare C1:D10 !
End Sub

Since working with Charles' single loop approach I've been
surprised to find it's faster than I expected. I'll
experiment using both methods with real life data, oh to
be spoilt with choice!

Many thanks,
Paul

-----Original Message-----
I'm not sure, but would any ideas here help you. I made
a 2-dim array
quickly from a worksheet. There might be a faster way,
but unsorted data
makes it a little harder.

Sub Demo()
'// Dana DeLouis
Dim v, MinLeft, ValueRight
v = [A1:B20]

With WorksheetFunction
MinLeft = .Min(.Index(v, 0, 1))
ValueRight = .Index(v, .Match(MinLeft, .Index(v,
0, 1), 0), 2)
End With
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the position
of
the minimum value in the first dimension and return the
value from the equivalent position in the second
dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons
do
not want to sort. If necessary my two dim array could
be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul


.

Hi Paul. Since you are looping, the same first Column array gets extracted
over and over. Perhaps you can extract that from the loop. Perhaps
something like this...

Sub Demo3()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight
Dim Column1
Dim i As Long

'Small test data
[A1:B20].Formula = "=RANDBETWEEN(1,100)"
[A1:B20] = [A1:B20].Value
v = [A1:B20]

With WorksheetFunction
'Keep next line out of loop...
Column1 = .Index(v, 0, 1)
For i = 1 To 10
MinLeft = .Small(Column1, i)
ValueRight = .Index(v, .Match(MinLeft, Column1, 0), 2)
Cells(i, 3).Resize(1, 2) = Array(MinLeft, ValueRight)
Next i
End With
End Sub

(I have the ATP installed for RANDBETWEEN( ) to work.)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens" wrote in message
...
Dana,

Very interesting, you've come up with what I had been
struggling to find.

In my reply to Charles I mentioned my additional "smallest
10 values problem", I've adapted your code:

Sub Demo2()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight, i
v = [A1:B20] 'populated with =RAND(), then
'pastespecial/values back
For i = 1 To 10
With WorksheetFunction
MinLeft = .Small(.Index(v, 0, 1), i)
ValueRight = .Index(v, .Match(MinLeft, _
.Index(v, 0, 1), 0), 2)
End With

Cells(i, 3) = MinLeft
Cells(i, 4) = ValueRight
Next
'sort A1:B20 with colA and compare C1:D10 !
End Sub

Since working with Charles' single loop approach I've been
surprised to find it's faster than I expected. I'll
experiment using both methods with real life data, oh to
be spoilt with choice!

Many thanks,
Paul

-----Original Message-----
I'm not sure, but would any ideas here help you. I made
a 2-dim array
quickly from a worksheet. There might be a faster way,
but unsorted data
makes it a little harder.

Sub Demo()
'// Dana DeLouis
Dim v, MinLeft, ValueRight
v = [A1:B20]

With WorksheetFunction
MinLeft = .Min(.Index(v, 0, 1))
ValueRight = .Index(v, .Match(MinLeft, .Index(v,
0, 1), 0), 2)
End With
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the position
of
the minimum value in the first dimension and return the
value from the equivalent position in the second
dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons
do
not want to sort. If necessary my two dim array could
be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul


.

Hi Dana,

Yes indeed, why didn't I think of that.
I've tested with larger volumes of data and Demo3 has
close to a 2:1 speed advantage, about 58% of Demo2 time
and well worth gaining.

I've pre-filled A1:B5000 with random data:

Sub demo3Pre()
Dim i As Long
For i = 1 To 5000
Cells(i, 1) = Rnd
Cells(i, 2) = Rnd
Next
End Sub

In the Demo subs I've encountering a problem with volumes
over 2730

v = [A1:B5000] 'no error
msgbox v(2730,0,1) ' no error
msgbox v(2731,0,1) ' error #9, cell values look OK

I have no idea of the significance of 2730 with this
range to array method. My real array is not populated this
way so the method works fine with larger volumes. However
I am curious about this 2730 limit.

Thanks (yet) again,
Paul

-----Original Message-----
Hi Paul. Since you are looping, the same first Column
array gets extracted
over and over. Perhaps you can extract that from the
loop. Perhaps
something like this...

Sub Demo3()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight
Dim Column1
Dim i As Long

'Small test data
[A1:B20].Formula = "=RANDBETWEEN(1,100)"
[A1:B20] = [A1:B20].Value
v = [A1:B20]

With WorksheetFunction
'Keep next line out of loop...
Column1 = .Index(v, 0, 1)
For i = 1 To 10
MinLeft = .Small(Column1, i)
ValueRight = .Index(v, .Match(MinLeft,
Column1, 0), 2)
Cells(i, 3).Resize(1, 2) = Array(MinLeft,
ValueRight)
Next i
End With
End Sub

(I have the ATP installed for RANDBETWEEN( ) to work.)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"
wrote in message
...
Dana,

Very interesting, you've come up with what I had been
struggling to find.

In my reply to Charles I mentioned my
additional "smallest
10 values problem", I've adapted your code:

Sub Demo2()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight, i
v = [A1:B20] 'populated with =RAND(), then
'pastespecial/values back
For i = 1 To 10
With WorksheetFunction
MinLeft = .Small(.Index(v, 0, 1), i)
ValueRight = .Index(v, .Match(MinLeft, _
.Index(v, 0, 1), 0), 2)
End With

Cells(i, 3) = MinLeft
Cells(i, 4) = ValueRight
Next
'sort A1:B20 with colA and compare C1:D10 !
End Sub

Since working with Charles' single loop approach I've
been
surprised to find it's faster than I expected. I'll
experiment using both methods with real life data, oh to
be spoilt with choice!

Many thanks,
Paul

-----Original Message-----
I'm not sure, but would any ideas here help you. I
made
a 2-dim array
quickly from a worksheet. There might be a faster way,
but unsorted data
makes it a little harder.

Sub Demo()
'// Dana DeLouis
Dim v, MinLeft, ValueRight
v = [A1:B20]

With WorksheetFunction
MinLeft = .Min(.Index(v, 0, 1))
ValueRight = .Index(v, .Match(MinLeft, .Index
(v,
0, 1), 0), 2)
End With
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the
position
of
the minimum value in the first dimension and return
the
value from the equivalent position in the second
dimension.

My array is a VBA variable, not a range reference,
and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other
reasons
do
not want to sort. If necessary my two dim array
could
be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul


.



.

Hi Paul. My guess is that you are using an earlier version of Excel. They
were limited in their ability to do array work. Here is one article...

XL7: Array Formulas Return #NUM! Error Value
http://support.microsoft.com/default...21&Product=xlw

it says in part..."This behavior occurs because in Microsoft Excel, the
maximum array size is 5458 elements "

Therefore, 5458 / 2 = 2729. A number like 2730 will therefore give you the
error you describe. This problem was fixed (and enhanced) in later
versions.

HTH.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens" wrote in message
...
Hi Dana,

Yes indeed, why didn't I think of that.
I've tested with larger volumes of data and Demo3 has
close to a 2:1 speed advantage, about 58% of Demo2 time
and well worth gaining.

I've pre-filled A1:B5000 with random data:

Sub demo3Pre()
Dim i As Long
For i = 1 To 5000
Cells(i, 1) = Rnd
Cells(i, 2) = Rnd
Next
End Sub

In the Demo subs I've encountering a problem with volumes
over 2730

v = [A1:B5000] 'no error
msgbox v(2730,0,1) ' no error
msgbox v(2731,0,1) ' error #9, cell values look OK

I have no idea of the significance of 2730 with this
range to array method. My real array is not populated this
way so the method works fine with larger volumes. However
I am curious about this 2730 limit.

Thanks (yet) again,
Paul

-----Original Message-----
Hi Paul. Since you are looping, the same first Column
array gets extracted
over and over. Perhaps you can extract that from the
loop. Perhaps
something like this...

Sub Demo3()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight
Dim Column1
Dim i As Long

'Small test data
[A1:B20].Formula = "=RANDBETWEEN(1,100)"
[A1:B20] = [A1:B20].Value
v = [A1:B20]

With WorksheetFunction
'Keep next line out of loop...
Column1 = .Index(v, 0, 1)
For i = 1 To 10
MinLeft = .Small(Column1, i)
ValueRight = .Index(v, .Match(MinLeft,
Column1, 0), 2)
Cells(i, 3).Resize(1, 2) = Array(MinLeft,
ValueRight)
Next i
End With
End Sub

(I have the ATP installed for RANDBETWEEN( ) to work.)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"
wrote in message
...
Dana,

Very interesting, you've come up with what I had been
struggling to find.

In my reply to Charles I mentioned my
additional "smallest
10 values problem", I've adapted your code:

Sub Demo2()
'// Dana DeLouis, adapted
Dim v, MinLeft, ValueRight, i
v = [A1:B20] 'populated with =RAND(), then
'pastespecial/values back
For i = 1 To 10
With WorksheetFunction
MinLeft = .Small(.Index(v, 0, 1), i)
ValueRight = .Index(v, .Match(MinLeft, _
.Index(v, 0, 1), 0), 2)
End With

Cells(i, 3) = MinLeft
Cells(i, 4) = ValueRight
Next
'sort A1:B20 with colA and compare C1:D10 !
End Sub

Since working with Charles' single loop approach I've
been
surprised to find it's faster than I expected. I'll
experiment using both methods with real life data, oh to
be spoilt with choice!

Many thanks,
Paul

-----Original Message-----
I'm not sure, but would any ideas here help you. I
made
a 2-dim array
quickly from a worksheet. There might be a faster way,
but unsorted data
makes it a little harder.

Sub Demo()
'// Dana DeLouis
Dim v, MinLeft, ValueRight
v = [A1:B20]

With WorksheetFunction
MinLeft = .Min(.Index(v, 0, 1))
ValueRight = .Index(v, .Match(MinLeft, .Index
(v,
0, 1), 0), 2)
End With
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"
wrote in message
...
In a two dimensional array I want to find the
position
of
the minimum value in the first dimension and return
the
value from the equivalent position in the second
dimension.

My array is a VBA variable, not a range reference,
and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other
reasons
do
not want to sort. If necessary my two dim array
could
be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet
functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul


.



.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will load the 1-D arrOutput with the values from the second
"column" of myArray corresponding to the 10 smallest values in the first
"column" of myArray:

Dim arrOutput(1 To 10)
For i = 1 To 10
arrOutput(i) = _
VLookups(Application.Small(Application.Index(MyArr ay, 0, 1), i), _
MyArray, 2)(1, 1)
Next

Alan Beban

Paul Stevens wrote:
In a two dimensional array I want to find the position of
the minimum value in the first dimension and return the
value from the equivalent position in the second dimension.

My array is a VBA variable, not a range reference, and
it's values are not in any order. Ubound is several
thousand so I don't want to loop and for other reasons do
not want to sort. If necessary my two dim array could be
reconstructed as two complimentary single dim arrays.

I've been messing around with various worksheet functions
(incl .Min), but I'm fumbling!

TIA for any help,
Paul

Thanks Alan,
I'll check this out tomorrow,
Paul