Loading/sorting array of Longs ( 65K rows)
 

I need to work with a couple of CSV files that have at least 350,000 rows --
too many to bring into Excel 2003. I can easily enough parse the columns into
an array, but I need to change the sorting so that I can then find a given
value in the array/table. If I do this with a simple row-by-row comparison,
it will take far too long. I found code that sorts long lists very
fast(http://www.devx.com/vb2themax/Tip/19470), but it is designed to work on
a single value per row. I can't easily figure out how to adapt it to my needs
so that it will sort a two- or three-column table. Can anyone help?

This is an enhancement to a large Excel macro. Changing entirely over to
Access is not an option. However, it's possible that enough of the users will
have Access on their machines that I could call Access from Excel VBA. I'm
checking into that now.
--
Gregg Roberts

Loading/sorting array of Longs ( 65K rows)
 

Never mind, thanks anyway, another solution is in the works.
--
Gregg Roberts

Loading/sorting array of Longs ( 65K rows)
 

Gregg,
Maybe another way...

Sub WhereIsIt()
'Jim Cone - San Francisco - July 2007
Dim i As Long
Dim j As Long
Dim A As Variant
Dim arr As Variant
Dim arr1 As Variant
Dim arr2 As Variant
Dim arr3 As Variant
Dim arr4 As Variant
Dim arr5 As Variant
Dim arr6 As Variant
Dim bThere As Boolean
Dim ArrTemp() As Long
Const GIVEN_VALUE As Long = 99999

ReDim ArrTemp(1 To 60000, 1 To 6)

'Fill the array
For i = 1 To 60000
For j = 1 To 6
ArrTemp(i, j) = Rnd * 360000 + 1
Next
Next
'Return each column into it's own array.
arr1 = Application.Index(ArrTemp, 0, 1) '1st column
arr2 = Application.Index(ArrTemp, 0, 2) '2nd column
arr3 = Application.Index(ArrTemp, 0, 3) '3rd column
arr4 = Application.Index(ArrTemp, 0, 4) '4th column
arr5 = Application.Index(ArrTemp, 0, 5) '5rd column
arr6 = Application.Index(ArrTemp, 0, 6) '6th column

'Store the arrays.
arr = Array(arr1, arr2, arr3, arr4, arr5, arr6)

'Find the lookup value in any of the columns
For i = 0 To 5
A = Application.Match(GIVEN_VALUE, arr(i), 0)
If Not IsError(A) Then
MsgBox GIVEN_VALUE & " found in column " & i + 1 & " at row " & A
bThere = True
End If
Next
If Not bThere Then MsgBox GIVEN_VALUE & " not Found "
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Gregg Roberts"

wrote in message
I need to work with a couple of CSV files that have at least 350,000 rows --
too many to bring into Excel 2003. I can easily enough parse the columns into
an array, but I need to change the sorting so that I can then find a given
value in the array/table. If I do this with a simple row-by-row comparison,
it will take far too long. I found code that sorts long lists very
fast(http://www.devx.com/vb2themax/Tip/19470), but it is designed to work on
a single value per row. I can't easily figure out how to adapt it to my needs
so that it will sort a two- or three-column table. Can anyone help?

This is an enhancement to a large Excel macro. Changing entirely over to
Access is not an option. However, it's possible that enough of the users will
have Access on their machines that I could call Access from Excel VBA. I'm
checking into that now.
--
Gregg Roberts

Loading/sorting array of Longs ( 65K rows)
 

Wow, thanks, Jim!

I tried a simplified version of Application.Match (which is a new method to
me). I get a Type Mismatch when there are 5000 rows in my array. With only
500 rows, the code doesn't throw an error, but A does get set to Error 2042.

Yet, your code works without error. So I'll try to adapt it to my needs with
as few changes as possible.

--
Gregg Roberts


"Jim Cone" wrote:

Gregg,
Maybe another way...

Sub WhereIsIt()
'Jim Cone - San Francisco - July 2007
Dim i As Long
Dim j As Long
Dim A As Variant
Dim arr As Variant
Dim arr1 As Variant
Dim arr2 As Variant
Dim arr3 As Variant
Dim arr4 As Variant
Dim arr5 As Variant
Dim arr6 As Variant
Dim bThere As Boolean
Dim ArrTemp() As Long
Const GIVEN_VALUE As Long = 99999

ReDim ArrTemp(1 To 60000, 1 To 6)

'Fill the array
For i = 1 To 60000
For j = 1 To 6
ArrTemp(i, j) = Rnd * 360000 + 1
Next
Next
'Return each column into it's own array.
arr1 = Application.Index(ArrTemp, 0, 1) '1st column
arr2 = Application.Index(ArrTemp, 0, 2) '2nd column
arr3 = Application.Index(ArrTemp, 0, 3) '3rd column
arr4 = Application.Index(ArrTemp, 0, 4) '4th column
arr5 = Application.Index(ArrTemp, 0, 5) '5rd column
arr6 = Application.Index(ArrTemp, 0, 6) '6th column

'Store the arrays.
arr = Array(arr1, arr2, arr3, arr4, arr5, arr6)

'Find the lookup value in any of the columns
For i = 0 To 5
A = Application.Match(GIVEN_VALUE, arr(i), 0)
If Not IsError(A) Then
MsgBox GIVEN_VALUE & " found in column " & i + 1 & " at row " & A
bThere = True
End If
Next
If Not bThere Then MsgBox GIVEN_VALUE & " not Found "
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Gregg Roberts"

wrote in message
I need to work with a couple of CSV files that have at least 350,000 rows --
too many to bring into Excel 2003. I can easily enough parse the columns into
an array, but I need to change the sorting so that I can then find a given
value in the array/table. If I do this with a simple row-by-row comparison,
it will take far too long. I found code that sorts long lists very
fast(http://www.devx.com/vb2themax/Tip/19470), but it is designed to work on
a single value per row. I can't easily figure out how to adapt it to my needs
so that it will sort a two- or three-column table. Can anyone help?

This is an enhancement to a large Excel macro. Changing entirely over to
Access is not an option. However, it's possible that enough of the users will
have Access on their machines that I could call Access from Excel VBA. I'm
checking into that now.
--
Gregg Roberts