[Vasant Nanavati[_2_]]

Hi Randy:

Is it column D or column B? In any event, you can sort from the menu using
Data | Sort and specify which column you want to sort on. Perhaps I'm
missing the point.

Regards,

Vasant.

"Randy S" wrote in message
...
How do I sort a 2-dimensional array from a formula? If I
have 2 columns of data:

Col A Col D
Joe Smith $4.24
Jane Doe $2.48
Fred Flint $8.88
John Stein $1.89


I want to sort both columns based on values in Col B, so
I'd end up with:

Col A Col D
Fred Flint $8.88
Joe Smith $4.24
Jane Doe $2.48
John Stein $1.89

[Alan Beban[_3_]]

Maybe he means a 2-D array rather than a range; though Col A and Col D
do look suspiciously like a range.

Alan

Vasant Nanavati wrote:
Hi Randy:

Is it column D or column B? In any event, you can sort from the menu using
Data | Sort and specify which column you want to sort on. Perhaps I'm
missing the point.

Regards,

Vasant.

"Randy S" wrote in message
...

How do I sort a 2-dimensional array from a formula? If I
have 2 columns of data:

Col A Col D
Joe Smith $4.24
Jane Doe $2.48
Fred Flint $8.88
John Stein $1.89


I want to sort both columns based on values in Col B, so
I'd end up with:

Col A Col D
Fred Flint $8.88
Joe Smith $4.24
Jane Doe $2.48
John Stein $1.89

[Randy S[_3_]]

I'm looking for a formula that I can enter to do this for
me automatically so I don't have to do Data-Sort
everytime the data changes. I specifically chose Col D to
show the two columns are not adjacent.


-----Original Message-----
Maybe he means a 2-D array rather than a range; though
Col A and Col D
do look suspiciously like a range.

Alan

Vasant Nanavati wrote:
Hi Randy:

Is it column D or column B? In any event, you can sort
from the menu using
Data | Sort and specify which column you want to sort
on. Perhaps I'm
missing the point.

Regards,

Vasant.

"Randy S" wrote in message
...

How do I sort a 2-dimensional array from a formula? If I
have 2 columns of data:

Col A Col D
Joe Smith $4.24
Jane Doe $2.48
Fred Flint $8.88
John Stein $1.89


I want to sort both columns based on values in Col B, so
I'd end up with:

Col A Col D
Fred Flint $8.88
Joe Smith $4.24
Jane Doe $2.48
John Stein $1.89




.

[J.E. McGimpsey]

Since formulas can't change the values in other cells, what you're
asking isn't possible. Even if they could, you'd have circular
references to deal with.

In addition, you don't really have a two-dimensional array - it's
really three-D since there are two 5x1 areas (which is why you get
the multiple selections error if you try to sort them manually).

Instead of a formula, you could use an event macro to sort when the
data changes. One way:

Put this in the worksheet code module (right-click the worksheet tab
and choose view code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 4 Then sortDandA
End Sub

and put this in a regular code module (insert/module in the VBE):

Public Sub sortDandA()
Dim arr1 As Variant
Dim arr2 As Variant
Dim temp1 As Variant
Dim temp2 As Variant
Dim rng As Range
Dim i As Integer
Dim j As Integer
Dim chg As Boolean

Set rng = Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
arr1 = rng.Value
arr2 = rng.Offset(0, -3).Value
Do
chg = False
For i = 2 To UBound(arr1, 1)
j = i - 1
If arr1(i, 1) arr1(j, 1) Then
temp1 = arr1(i, 1)
temp2 = arr2(i, 1)
arr1(i, 1) = arr1(j, 1)
arr2(i, 1) = arr2(j, 1)
arr1(j, 1) = temp1
arr2(j, 1) = temp2
chg = True
End If
Next i
Loop Until chg = False
Application.EnableEvents = False
rng.Value = arr1
rng.Offset(0, -3).Value = arr2
Application.EnableEvents = True
End Sub

This is fast for relatively small amounts of data. For large
amounts, a faster sort routine should be used.


In article ,
"Randy S" wrote:

I'm looking for a formula that I can enter to do this for
me automatically so I don't have to do Data-Sort
everytime the data changes. I specifically chose Col D to
show the two columns are not adjacent.