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

strat919

Board Regular
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Yongle

Well-known Member
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:

strat919

Board Regular
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!

Replies
0
Views
306
Replies
8
Views
134
Replies
4
Views
680
Replies
2
Views
347
Replies
5
Views
435

Threads
1,147,477
Messages
5,741,364
Members
423,657
Latest member
Medrok2021

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

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