PDA

View Full Version : Sorting data over multiple sheets into array



Danish
03-07-2007, 09:14 AM
We have a large data set distributed across to worksheets in the same workbook.

The two sheets are named: MAK & MKW
The first observation in each sheet is located in cell B10 and ends in FZ10
the corresponding names to the data is located in cell B4 and ends in FZ4 in
both sheets.

We need to sort row 10 in ascending order and it is important that the names
are linked to their observation and finally load it into an array.

So we end up with an 2*362 array/matrix sorted according to the observations
(it is not important for us to see it explicitly in a new worksheet)

It is important that it is VBA code because we have over 400 rows and we don't want do it in access.

Thank you

XLGibbs
03-07-2007, 04:58 PM
YOu need to sort row 10 downward in ascending order? or you need row 10 sorted ascending HORIZONTALLY?

You could record a macro that does the sort process and that can be tweeked to be automated if need be as well... (also a recorded macro can be re-executed without alterations, but it is better to modify the recorded code to be more efficient)..

Danish
03-08-2007, 01:39 AM
We need row 10 to be sorted ascending horizontally across the two worksheets so we end up with one array containing the observation and its corresponding name.

Charlize
03-08-2007, 07:46 AM
sorting for one sheet.Sub sort()
'sort on row 10 and column B4 moves along together with data starting from FZ11
Dim lastrow As Long
lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("B4", "B10:FZ" & lastrow).sort Key1:=Range("B10"), Order1:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End Sub
Charlize