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

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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:
(17.695366,-64.882509)

<colgroup><col width="169"></colgroup><tbody>
</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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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)

Code:
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
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top