Return location match/matches in a radius.

[SlightlyClueless]

Hello All,

For the past day now I have been working on a solution to return locations that fall within a 1/4 mile and 1/2 mile radius of a specific site. There is a list of about 3,000 sites that would need to be bounced off a portfolio (10,000+ locations) to see if they fell within a 1/4 mile and 1/2 mile radius of a location/locations in the portfolio. All the location data is Decimal Degrees format. In order to get things working properly first, I made a small mock data set to work with. A link to google drive to download that workbook is below. So far I have been able to get a site to return the closest match from the portfolio and the distance the site is from the portfolio match. However, in the real dataset along with the test data set, there may be more than one match from the portfolio. What do is needed to be done in order for each site to list the match/matches from the portfolio that fall in a 1/4 mile and 1/2 mile radius?

Site List

Distance formula

=INDEX(ACOS(COS(RADIANS(90-Portfolio!$C$2:$C$6)) *COS(RADIANS(90-C2)) +SIN(RADIANS(90-Portfolio!$C$2:$C$6)) *SIN(RADIANS(90-C2)) *COS(RADIANS(Portfolio!$D$2:$D$6-D2)))*3958.756,MATCH(SMALL((ABS(C2-Portfolio!$C$2:$C$6)^2+ABS(D2-Portfolio!$D$2:$D$6)^2)^(0.5),1),(ABS(C2-Portfolio!$C$2:$C$6)^2+ABS(D2-Portfolio!$D$2:$D$6)^2)^(0.5),0))

Nearest Location formula

=INDEX(Portfolio!$A$2:$A$6,MATCH(SMALL((ABS(C2-Portfolio!$C$2:$C$6)^2+ABS(D2-Portfolio!$D$2:$D$6)^2)^(0.5),1),(ABS(C2-Portfolio!$C$2:$C$6)^2+ABS(D2-Portfolio!$D$2:$D$6)^2)^(0.5),0))

Portfolio

Google Drive Link

https://drive.google.com/uc?export=download&id=1eaDyUrjGjonV6iJkMa1Tac2v6NfquIsx

 

[KRice]

The links do not seem to be working. What version of Excel are you using?

 

[SlightlyClueless]

The links do not seem to be working. What version of Excel are you using?

Sorry about that! I had turned the link into a direct download link so that it wouldn't load right into google sheets. The regular link to the sheet is

https://docs.google.com/spreadsheets/d/1eaDyUrjGjonV6iJkMa1Tac2v6NfquIsx/edit?usp=sharing&ouid=102720600064113628014&rtpof=true&sd=true

Office 365 for business is installed on this PC

 

[KRice]

I would suggest something like this...
Convert your data on the Portfolio sheet to an official Excel table (click anywhere in data range and then Ctrl-t and confirm that you have table headers. Note that I added a location far away to confirm that it is being correctly filtered in the next step. I also named this table tblPortfolio. The advantage here is that tables can expand/shrink and the ranges referenced do not require further adjustments because the formula uses structured references (refers to column heading names).

Test Location Book v1.xlsx
	A	B	C	D
1	Site ID	Site Name	Latitude	Longitude
2	Portfolio B-1	Dental	26.00755	-80.248754
3	Portfolio B-2	Hospital	26.03045	-80.246743
4	Portfolio B-3	Home Depot	25.99561	-80.203481
5	Portfolio B-4	Hard Rock	26.05504	-80.211548
6	Portfolio B-5	Publix	26.01383	-80.250659
7	Port C-1	Pub	35	-90
Portfolio

Then to eliminate redundancies in the formulas and to more efficiently find those that meet your distance criteria, use a variety of functions available in Excel 365.
I've modified the formula used in the results table to offer some clarity about what the formula does. The results in the lower explanatory table show what is happening behind the scenes for the green cell above. In particular, we create an array of distances and then horizontally append that array to the main data array (done by HSTACK). Then we can filter this array to extract only those rows where the distance criterion is met. Finally, we can sort this array by distance and then return only the locations associated with those distances. The lower formula is not needed.

Test Location Book v1.xlsx
	A	B	C	D	E	F
1					0.25	0.5
2	Site ID	Site Name	Latitude	Longitude	Locations within distance	Locations within distance
3	Site 1	Goodwill	26.01108058	-80.25058174	Publix, Dental, Hospital, Home Depot, Hard Rock	Publix, Dental, Hospital, Home Depot, Hard Rock
4	Site 2	Lowes	26.01149703	-80.25072055	Publix, Dental, Hospital, Home Depot, Hard Rock	Publix, Dental, Hospital, Home Depot, Hard Rock
5	Site 3	Fictitious	35.01	-90.01	Pub	Pub
6
7		Portfolio B-5	Publix	26.01383088	-80.25065913	0.002751383
8		Portfolio B-1	Dental	26.00754567	-80.24875423	0.003979368
9		Portfolio B-2	Hospital	26.03044507	-80.24674283	0.019741341
10		Portfolio B-3	Home Depot	25.99561025	-80.20348149	0.049575848
11		Portfolio B-4	Hard Rock	26.05503966	-80.21154788	0.05878811
Sites

Cell Formulas
Range	Formula
E3:F5	E3	=LET(dist,(($C3-tblPortfolio[Latitude])^2+($D3-tblPortfolio[Longitude])^2)^0.5,TEXTJOIN(", ",,CHOOSECOLS(SORT(FILTER(HSTACK(tblPortfolio,dist),dist<=E$1),5),2)))
B7:F11	B7	=LET(dist,(($C3-tblPortfolio[Latitude])^2+($D3-tblPortfolio[Longitude])^2)^0.5,res,SORT(FILTER(HSTACK(tblPortfolio,dist),dist<=E$1),5),res)
Dynamic array formulas.

 

[KRice]

This distance formula (dist) can be replaced by any of several spherical distance formulas if preferred. As you'll note, the simpler Pythagorean approach is used in my formulation, and it probably offers sufficient accuracy for such short distances where "flat Earth" assumptions are reasonable.

 

[KRice]

Sorry...I was more focused on working with the arrays and not paying attention to the distance formula. What I had is not correct for distance...I'm assuming you are happy with the ACOS formula for distance. If so, then here it is incorporated into my earlier offering. I've changed the green cell so that you can see the array returned by the formula (which in this case has two entries).

Test Location Book v1.xlsx
	A	B	C	D	E	F
1					0.25	0.5
2	Site ID	Site Name	Latitude	Longitude	Locations within distance	Locations within distance
3	Site 1	Goodwill	26.01108058	-80.25058174	Publix	Publix, Dental
4	Site 2	Lowes	26.01149703	-80.25072055	Publix	Publix, Dental
5	Site 3	Fictitious	35.001	-90.001	Pub	Pub
6
7		Portfolio B-5	Publix	26.01383088	-80.25065913	0.190087745
8		Portfolio B-1	Dental	26.00754567	-80.24875423	0.269314575
Sites

Cell Formulas
Range	Formula
E3:F5	E3	=LET(lat,tblPortfolio[Latitude],long,tblPortfolio[Longitude], dist,ACOS(COS(RADIANS(90-lat)) *COS(RADIANS(90-$C3)) +SIN(RADIANS(90-lat)) *SIN(RADIANS(90-$C3)) *COS(RADIANS(long-$D3)))*3958.756, TEXTJOIN(", ",,CHOOSECOLS(SORT(FILTER(HSTACK(tblPortfolio,dist),dist<=E$1),5),2)))
B7:F8	B7	=LET(lat,tblPortfolio[Latitude],long,tblPortfolio[Longitude], dist,ACOS(COS(RADIANS(90-lat)) *COS(RADIANS(90-$C3)) +SIN(RADIANS(90-lat)) *SIN(RADIANS(90-$C3)) *COS(RADIANS(long-$D3)))*3958.756, SORT(FILTER(HSTACK(tblPortfolio,dist),dist<=F$1),5))
Dynamic array formulas.

 

[SlightlyClueless]

@KRice You are Amazing! Your solution is exactly what I was looking to do! My feeble brain just couldn't put it together.

 

[KRice]

I'm happy to help. By appending the distance array to the data as a first step (using HSTACK), we can then extract relevant information and process it further in the other steps...in this case FILTER those meeting the distance threshold and then SORT, with the closer distances listed first. You could also return the estimated "straight-line" distances together with the locations, but I wasn't sure about your final preferred outputs...sounds as if TEXTJOIN of locations in a single cell is desired.