List of all Combinations of Row with 10's of Millions of Combinations

[strat919]

I've been working on this for days. I have lists of latitude longitude of 10 to 20 thousand entries. This results in 10's of millions of combinations....or more. Excel cannot handle much more than a million rows.

I have been trying various methods which work as long as the result is within the million row excel limitation.

What I'm trying to do is create a list of all combinations of lat long with a list of 10 to 20 thousand entries, then use a formula to determine the distances between the combinations so I can sort and find which combinations are very close to each other.

With a small list I can successfully create the desired result by combining the lat and long coordinates to one cell, separated by a comma. Say there are 200 rows with coordinates... Example:

<colgroup><col width="169"></colgroup><tbody>
[TD="class: xl63, width: 169"](17.695366,-64.882509)
[/TD]

</tbody>

Then using this formulas.....
Public Sub GetUniquePairs()

Dim lastRow As Long
Dim thisRow As Long
Dim i As Long
Dim j As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
thisRow = 1
For i = 1 To lastRow - 1
For j = i + 1 To lastRow
Cells(thisRow, 2).Value = Cells(i, 1).Value & "," & Cells(j, 1).Value
thisRow = thisRow + 1
Next j
Next i

End Sub

The results look like (95366,-64.882509,17.733305,-64.679077) which I can delimit out to create 4 columns which is what I need to apply the formula for determining the distance between the 2 sets of coordinates.
I'm using =6371*ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-E1))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-E1))*COS(RADIANS(B1-F1)))/1.609 to determine distances.

All this works perfectly for a smaller number of calculations... but with the million rows limitation, I cannot use this

I have been trying to solve this with power pivot, but with the results from power pivot.... It will not let me delimit to get the correct format for the final calculation.

I hope there is a genius out there that can help me solve this

Thanks

 

[Yongle]

Try this and run from sheet containing your list of coordinates in column A
- code below writes to a new sheet after 500000 rows which can be set to any value you want (up to 1048576)

Public Sub GetUniquePairs()
    Application.ScreenUpdating = False
    Const maximum = [COLOR=#ff0000]500000[/COLOR]
    Dim lastRow As Long, thisRow As Long
    Dim i As Long, j As Long
    Dim ws As Worksheet, Results As Worksheet
    Dim Res(1 To maximum, 1 To 1) As Variant
    
    Set ws = ActiveSheet
    Set Results = Sheets.Add
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    thisRow = 1
    
    For i = 1 To lastRow - 1
        For j = i + 1 To lastRow
            c = c + 1
            Res(thisRow, 1) = ws.Cells(i, 1).Value & "," & ws.Cells(j, 1).Value
            thisRow = thisRow + 1
                If thisRow = maximum Then
                    thisRow = 1
                    Results.Cells(1, 1).Resize(maximum).Value = Res
                    Erase Res
                    Set Results = Sheets.Add
                End If
                If i = lastRow - 1 Then
                    Results.Cells(1, 1).Resize(maximum).Value = Res
                    Erase Res
                End If
        Next j
    Next i
End Sub

 

[strat919]

Thanks so much for the response! I tried this and it works. But managing the data would be difficult and very time consuming. I was hoping this could be handled with power pivot somehow.

If anyone has any other suggestions, please let me know. I have been working on a project for 7 months and this must be solved somehow for the project.

Any help would be Greatly appreciated!