Sorting data over multiple sheets into array
 

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.

Thank you

Sorting data over multiple sheets into array
 

Have you considered using an Access database? You would be able to set up as
many columns as needed without the hassle of having data spread over separate
datasheets.
--
Best wishes,

Jim


"The danish student" wrote:

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.

Thank you

Sorting data over multiple sheets into array
 

Yes we have considered it but we need to use Excel because our data is linked
to this program.

thanks anyway

"Jim Jackson" wrote:

Have you considered using an Access database? You would be able to set up as
many columns as needed without the hassle of having data spread over separate
datasheets.
--
Best wishes,

Jim


"The danish student" wrote:

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.

Thank you

Sorting data over multiple sheets into array
 

Sub SortedArray()
Dim myArray(1 To 2, 1 To 362)
Dim iCt As Integer
Dim iCt2 As Integer

Worksheets.Add After:=Worksheets(Worksheets.Count)
Sheets("MAK").Range("B4:FZ4").Copy
Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MAK").Range("B10:FZ10").Copy
Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MKW").Range("B4:FZ4").Copy
Range("A182").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MKW").Range("B10:FZ10").Copy
Range("B182").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Range("A1:B362").Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
For iCt = 1 To 2
For iCt2 = 1 To 362
myArray(iCt, iCt2) = Cells(iCt2, iCt)
Next iCt2
Next iCt
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

Hth,
Merjet

Sorting data over multiple sheets into array
 

Thank you "merjet"

It works fine.

However is it possible to incorporate a loop in this data sorting code? We
have data until row 414 and would like your sorting code to run through all
414 rows!

thanks in advance

"merjet" wrote:

Sub SortedArray()
Dim myArray(1 To 2, 1 To 362)
Dim iCt As Integer
Dim iCt2 As Integer

Worksheets.Add After:=Worksheets(Worksheets.Count)
Sheets("MAK").Range("B4:FZ4").Copy
Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MAK").Range("B10:FZ10").Copy
Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MKW").Range("B4:FZ4").Copy
Range("A182").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MKW").Range("B10:FZ10").Copy
Range("B182").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Range("A1:B362").Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
For iCt = 1 To 2
For iCt2 = 1 To 362
myArray(iCt, iCt2) = Cells(iCt2, iCt)
Next iCt2
Next iCt
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

Hth,
Merjet

Sorting data over multiple sheets into array
 

So that we finally get 400 sets of company names and data in the new sheets.

"The danish student" wrote:

Thank you "merjet"

It works fine.

However is it possible to incorporate a loop in this data sorting code? We
have data until row 414 and would like your sorting code to run through all
414 rows!

thanks in advance

"merjet" wrote:

Sub SortedArray()
Dim myArray(1 To 2, 1 To 362)
Dim iCt As Integer
Dim iCt2 As Integer

Worksheets.Add After:=Worksheets(Worksheets.Count)
Sheets("MAK").Range("B4:FZ4").Copy
Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MAK").Range("B10:FZ10").Copy
Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MKW").Range("B4:FZ4").Copy
Range("A182").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Sheets("MKW").Range("B10:FZ10").Copy
Range("B182").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Range("A1:B362").Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
For iCt = 1 To 2
For iCt2 = 1 To 362
myArray(iCt, iCt2) = Cells(iCt2, iCt)
Next iCt2
Next iCt
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

Hth,
Merjet

Sorting data over multiple sheets into array
 

I don't follow. A loop to do what? Excel does the sorting on the
temporarily added sheet. You originally said you had 181 observations
on two sheets (thus 362 total). Now you say 414 or 400 observations.
Where are the extra ones? If you want to handle more observations,
adapt the hard-coded parts (worksheet names, columns, rows and array
size) of the macro I gave you. You could, of course, replace them with
variables, but you would have to give them specific values somehow.

Merjet