Sort Values In Array

[Nick_London]

Hi,

I have the code below (compiled using help from this site previously) which takes data, runs each series through a formula and then spits out the results in a spreadsheet. I have been using this code for a while but I would now like to develop it further and my VBA knowledge with it.



Sub Run()

Dim arr(), x As Long, y As Long
Dim Ydata As Range, Xdata As Range, Zdata As Range, Head As Range

With Sheets("Variables")
Set Xdata = Range(.Cells(15, 3), .Cells(15, 3).End(xlToRight).End(xlDown))
x = Xdata.Columns.Count
y = Xdata.Rows.Count

Set Head = .Cells(1, 3).Resize(, x)
End With

Set Ydata = Sheets("Calculate").Cells(15, 4).Resize(y)
Set Zdata = Sheets("Calculate").Cells(15, 14).Resize(y)

ReDim arr(x, 3)

With Sheets("Results")
For i = 1 To x
arr(i, 1) = Head(i)
arr(i, 2) = Application.WorksheetFunction.LinEst(Ydata.Value, Xdata.Columns(i).Value, True, True)(1, 1)
arr(i, 3) = Application.WorksheetFunction.LinEst(Ydata.Value, Xdata.Columns(i).Value, True, True)(1, 3)

Next
End With
Sheets("Results").Range("A2").Resize(x, 3) = arr()

End Sub


Any ideas on what is the most efficient/fastest way to sort the data (max to min) in the array by column 2 and paste the sorted results into the spreadsheet instead of the unsorted. Could something like this be used?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=103

I know I can sort the data in the spreadsheet but ultimately my objective is to extract the raw variables (i) interatively in order of the highest ranked stats in column 2 of the array and place into a column of a worksheet.

But I wanna take one step at a time so start with sort first.

Thanks,

Nick

[Paul_Hossler]

The KB code seems to be a bubble sort.

Chip has very good code for a Quick Sort VBA alogrithm that usually suffices for me

http://www.cpearson.com/excel/SortingArrays.aspx


Paul

[mikerickson]

It would probably be faster to put the data into the worksheet and then use Excel's .Sort method than to bubble sort in VBA before writing to the sheet.

[snb]

Why don't you use ?


With Sheets("Variables")
Set Xdata = .Cells(15, 3).currentregion

[Nick_London]

Thanks guys.

If it's gonna be faster to sort in excel then it is not probably worth the effort trying to write code to do the sort in VBA. So maybe it will be more productive to focus on other elements.

If I did my sort in say sheet("Results").Range("A1:C200") and after sorting my highly ranked variables are at the top. How would I be able to extract say the first variable at the top (A2) from the Xdata array and place into say column G and then do the next variable (fot the 10 most ranked variables)

Would that be something like:


For z = 2 To 10
Cells(z, 1).Value
Match Head in Xdata
xdata(i).copy

Sheets("Results").Range("U14").PasteSpecial Paste:=xlPasteValues

'do stuff

next z


The key bit I am not sure how to do is extracting each variable from the results sheet from the xdata array and placing into the sheet.

SNB: Is there a reason for defining Xdata like this than how it's currently defined, i.e would it run quicker? (note I did not write the orginal code but just modified it!)

Thanks,

Nick

[snb]

I'd use:


Sub snb()
xdata=Sheets("Variables").Cells(15, 3).currentregion
Ydata = Sheets("Calculate").Cells(15, 4).Resize(ubound(xdata))
' ??? Zdata = Sheets("Calculate").Cells(15, 14).Resize(ubound(xdata))

For i = 1 To x
xdata(i, 2) = Application.LinEst(Ydata, index(Xdata,,i), True, True)(1, 1)
xdata(i, 3) = Application.LinEst(Ydata, Index(Xdata,,i), True, True)(1, 3)
Next

Sheets("Results").Range("A2").Resize(x, 3) = xdata
End Sub

[Nick_London]

Thanks Snb.

I tried running your code. First it gave me an error relating to the index function which I changed from index to application.index - don't know if this is the right thing to do:

Xdata(i, 2) = Application.LinEst(Ydata, Application.Index(Xdata, , i), True, True)(1, 1)

Now it's giving me an application defined /object defined error relating to this line:

Sheets("Results").Range("A2").Resize(x, 3) = Xdata

Note that I think the orginal macro was set up the way it was partly because the linest function cannot accept variable names in the arguments, only values hence headers were defined separately.

Nick

[snb]

Sub snb()
xdata=Sheets("Variables").Cells(15, 3).currentregion
Ydata = Sheets("Calculate").Cells(15, 4).Resize(UBound(xdata))

ReDim arr(ubound(xdata,2), 3)
For i = 1 To 3
arr(i,1) = xdata(1,i)
arr(i, 2) = Application.LinEst(Ydata, application.index(Xdata,,i), True, True)(1, 1)
arr(i, 3) = Application.LinEst(Ydata, application.Index(Xdata,,i), True, True)(1, 3)
Next

Sheets("Results").Range("A2").Resize(ubound(arr)+1, 3) = arr
End Sub

[Nick_London]

Thanks SNB.

I tried this code but it giving me a Run type error 13 - Type mismatch

Debugging takes me to the line:

arr(i, 2) = Application.LinEst(Ydata, Application.Index(Xdata, , i), True, True)(1, 1)

Nick

[snb]

Sub snb()
head=Sheets("Variables").Cells(15, 3).currentregion.rows(1)
xdata=Sheets("Variables").Cells(15, 3).currentregion.offset(1)
Ydata = Sheets("Calculate").Cells(15, 4).Resize(UBound(xdata))


ReDim arr(UBound(xdata,2), 3)
For i = 1 To 3
arr(i,1) = head(1,i)
arr(i, 2) = Application.LinEst(Ydata, application.index(Xdata,,i), True, True)(1, 1)
arr(i, 3) = Application.LinEst(Ydata, application.Index(Xdata,,i), True, True)(1, 3)
Next


Sheets("Results").Range("A2").Resize(UBound(arr)+1, 3) = arr
End Sub

[mikerickson]

When doing a 2 dimensional sort, you can save time by using a 1-D indexing array and sorting that.

'pseudo-code

Dim DataRange as Range
Dim DataArray As Variant, SortedArray As Variant
Dim IndexArray() As Long

DataArray = DataRange.Value
Redim IndexArray (1 to DataRange.Rows.Count)

Rem make IndexArray = {1,2,3,...,n}
For i = 1 to UBound(indexArray)
IndexArray(i) = i
Next i

For i = 1 to UBound(IndexArray) - 1
For j = i + 1 To UBound(IndexArray)
If DataArray(IndexArray(i), 1)) < DataArray(IndexArray(i), 1) Then
Temp = IndexArray(i)
IndexArray(i) = IndexArray(j)
IndexArray(j) = Temp
End If
Next j
Next i

Rem output the data array on the sorted index array

Redim SortedArray(1 to DataRange.Rows.Count, 1 To DataRange.Columns.Count)
For i = 1 To UBound(IndexArray)
For j = 1 to DataRange.Columns.Count
SortedArray(i, j) = DataArray(IndexArray(i), j)
Next j
Next i

This approach cuts the number of swaping operations by a factor of the number of columns involved

[Nick_London]

Thanks SNB.

Still getting the same data error!

Mike - Do you think this would be any faster than sorting in Excel?

Thanks,

Nick

[mikerickson]

How many columns? How many rows?
To improve on Excel's native sort, I'd quick sort the indexing array rather than bubble sorting as in the example I posted.

Yes, with large data sets, quick sorting in VBA can be quicker than Excel's native sort function. I'd start thinking about a VBA sort at around 5,000 rows of data.

[snb]

To sort a multidimensional array see the attachment.

[Nick_London]

I'm looking at less than 300 rows and 3 columns so don't know if there would be any productivity saving!

Also I'll post a workbook example of my setup, data and results to make things easier to follow.

[Nick_London]

Hello again,

I've attached an example.

The Clicking orginal button executes the existing code and the SNB button the code written by SNB.

Hopefully this will provide a better explanation of why the new code is not working.

Thanks,

Nick

[snb]

Sub snb()
Head = Sheets("Variables").Cells(1).CurrentRegion.Rows(1).Offset(, 2).SpecialCells(2)
Xdata = Sheets("Variables").Cells(15, 3).CurrentRegion.Offset(1).SpecialCells(2)
Ydata = Sheets("Calculate").Cells(15, 4).Resize(UBound(Xdata))
ReDim arr(UBound(Xdata, 2), UBound(Head, 2))
For i = 1 To UBound(arr, 2)
arr(i, 1) = Head(1, i)
arr(i, 2) = Application.LinEst(Ydata, Application.Index(Xdata, , i + 2))(1)
arr(i, 3) = Application.LinEst(Ydata, Application.Index(Xdata, , i + 2))(2)
Next

Sheets("Results").Range("G2").Resize(UBound(arr) + 1, 3) = arr
End Sub

[snb]

revised/simplified:


Sub snb()
Head = Sheets("Variables").Cells(1).CurrentRegion.Rows(1).Offset(, 2).SpecialCells(2).Resize(3)

Xdata = Sheets("Variables").Cells(15, 3).CurrentRegion
Ydata = Sheets("Calculate").Cells(15, 4).Resize(UBound(Xdata))

For j = 1 To UBound(Head, 2)
Head(2, j) = Application.LinEst(Ydata, Application.Index(Xdata, , j + 2))(1)
Head(3, j) = Application.LinEst(Ydata, Application.Index(Xdata, , j + 2))(2)
Next

Sheets("Results").Range("G2").Resize(UBound(Head, 2), UBound(Head)) = Application.Transpose(Head)
End Sub

[Nick_London]

Thanks SNB.

These seem to work.

Couple of questions.

1) In the line, if I wanted to return say row 4 of 1st column of the linest output instead of row 1 of the second column as currently, what changes would I need to make?

Head(3, j) = Application.LinEst(Ydata, Application.Index(Xdata, , j + 2))(2)

2) Going back to my orginal question, if I added code to the macro to sort the data by the max returned value in Excel, i.e by column H in the results sheet (which ranks X4 in the current data set at the top with highest value) how could I extract that data to say column P in the results sheet and then do the next ranked variable?

Thanks,

Nick

[snb]

Please illustrate in your sample workbook what you are looking for.

I prefer illustrations instead of descriptions.

[Nick_London]

OK SNB,

Here is a bit more illustration.

Question 1

I have inserted the output for linest function in cells I15:J19 in the sheet calculate using X1 as the example variable. Currently the macro brings back row 1 of column 1 and 2 of the array output. But If I wanted to bring back say row 4 of column 1 how would I do that? Not that to get all values from the output you need to specfify True, True which tells it to return all statistics.

Question 2

After runing your code, I am now calling the sort macro which sorts the results by column H with places variable X4 at the top. Now if I wanted to extract X4 from the Xdata array that was created earlier at the start of the macro and place the results in say column P and then do the next ranked variable (X5) and place results in column P - how would I do this?

So for illustration I have placed the data for the first variable in column P in terms of what it would look like. I would do something with this variable/data and then do something with the next variable.

Thanks,

Nick

[snb]

Sub snb()
Xdata = Sheets("Variables").Cells(15, 3).CurrentRegion
Ydata = Sheets("Calculate").Cells(15, 4).Resize(UBound(Xdata))

sq = Application.LinEst(Ydata, Application.Index(Xdata, , 3), True, True)

With Sheets("calculate").Cells(1, 12)
.Resize(UBound(sq), UBound(sq, 2)) = sq
.CurrentRegion.sort .Cells(1, 12)
End With
End Sub

[Nick_London]

Thanks SNB,

I ran this code but it generated a Run-time error 1004. "The sort reference is not valid. Make sure that it's within the data you want to sort"

Debugging takes me to:
.CurrentRegion.Sort .Cells(1, 12)

Also I'm a bit confused about what this latest code is doing. From looking at the output it looks it is returns the entire results from the linest function of the first variable? However I only want to display the values in row 1 and row 4 of column 1 of the linest function output by each X variable, i.e the data in the same format as the orginal macro: The outputs from the macro in the first column would be identical, the output in the 2nd column would now reflect the values of row4 of the 1st column of the linest array output.

Y12.73E-12X16.560983849.156X28.8569363866.079X39.0964293994.563X431.129214596.402X5 10.075923752.257X65.1073094285.768X70.9711793740.098

Nick

[snb]

Analyse the code and you will be able to adapt it yourself;


Sub snb()
Xdata = Sheets("Variables").Cells(15, 3).CurrentRegion
Ydata = Sheets("Calculate").Cells(15, 4).Resize(UBound(Xdata))

sq = Application.LinEst(Ydata, Application.Index(Xdata, , 3), True, True)

With Sheets("calculate").Cells(1, 12)
.Resize(UBound(sq), UBound(sq, 2)) = sq
.CurrentRegion.sort .parent.Cells(1, 12)
End With
End Sub

[Nick_London]

Ok,

I manage to make a few changes that now gives me the right outputs from the linest function and also let me me extract variables at the end but I had to restructure the macro to do so.

Is this what you meant SNB?


Sub snb_latest()
Xdata = Sheets("Variables").Cells(15, 3).CurrentRegion
Ydata = Sheets("Calculate").Cells(15, 4).Resize(UBound(Xdata))

'sq = Application.LinEst(Ydata, Application.Index(Xdata, , 3), True, True)

sq = Application.Index(Application.LinEst(Ydata, Application.Index(Xdata, , 4), True, True), 1, 1)
sq2 = Application.Index(Application.LinEst(Ydata, Application.Index(Xdata, , 4), True, True), 4, 1)
'number in bracket gives the column to return results for
Sheets("calculate").Cells(1, 12).Value = sq
Sheets("calculate").Cells(2, 12).Value = sq2
'.Resize(UBound(sq), UBound(sq, 2)) = sq
'.CurrentRegion.Sort .Parent.Cells(1, 12)
'End With

'SORT RESULTS

xcol = Application.Index(Xdata, , 4)
Sheets("calculate").Cells(1, 14).Resize(UBound(Xdata)) = xcol

End Sub