sort 2 dimensional grid

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
i have a 2d grid

3m 6m 1y
1y 2 3 9
2y 3 7 8
3y 9 8 4

I want to sort by value so that output looks like

3mx1y 2
3mx2y 3
6mx2y 3
1yx3y 4

and so on. any idea's?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub test()
Dim a(1 To 4, 1 To 4), result(), i As Long, ii As Long, txt As String
ReDim result(1 To UBound(a,2) - 1), 1 To 2)
a(1,2)="3m":a(1,3)="6m":a(1,4)="1y"
a(2,1)=1y:a(2,2)=2:a(2,3)=3:a(2,4)=9
a(3,1)="2y":a(3,2)=3:a(3,3)=7:a(3,4)=8
a(4,1)="3y":a(4,2)=9:a(4,3)=8:a(4,4)=4

For i =2 To UBound(a,2)
   result(i-1,1)=a(1,i) & "x" & a(i,1)
   With Application.WorksheetFunction
      result(i-1,2) = .Min(.Index(a,0,i))
   End With
Next
For i = 1 To UBound(a,1)
   For ii = 1 To UBound(a,2)
      txt = txt & a(i,ii) & vbTab
   Next
   txt = txt & vbLf
Next
MsgBox txt
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,172
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top