Index and partial match with multiple criteria across two sheets

[rrands1]

Hi all - I have two workbooks that I am trying to do partial matches across, but have gotten totally lost, so rather than post what I have tried, I thought it more expedient to post what I am after. Any help is greatly appreciated!

brief description of sheets:
Sheet1:
Contains columns of Latitude, Longitude, and "Type" (Type can be House or Business)
Sheet2:
Contains columns of similar Latitude, Longitude, and an empty "Type" column that I want to populate. The degree of precision in the Latitude and Longitude data is different than Sheet 1, so while there are enough digits to get it close enough, it is not an exact match with Sheet1's data (thus the need for partial match)

Background & Goal:
Using Excel 2019 (if that matters)
For latitude, I want to match on the first (left-most) 6 digits (i.e. 33.39.) (sheet 1 as the source)
For longitude, I want to match on the first (left-most) 9 digits (i.e. -111.6363) (sheet 1 as the source)
When a partial match on both is found, I want to take the value from "Type" in Sheet 1, and copy it to the "Type" column in Sheet 2

I have gotten the code to loop through each line in Sheet 1 working OK, but I am getting hung up on the Index and Match part, as it does not seem to be matching on the partial, even with appending a "*" at the end of it.

Below are two mini-sheets (for Sheet1 and Sheet2, respectively):

residential 74 - exported from TH.xlsx
	A	B	C
1	Location type	Latitude	Longitude
2	House	33.39484879	-111.6363394
3	House	33.39493249	-111.6410672
4	House	33.3953966	-111.6411081
5	House	33.39492689	-111.6405243
6	House	33.39543915	-111.6404992
7	House	33.39486475	-111.6399115
8	House	33.39550353	-111.6399654
9	House	33.39484459	-111.6395165
10	House	33.3954537	-111.6389164
11	House	33.39485635	-111.6388211
12	House	33.39543551	-111.6385087
13	House	33.39491961	-111.6383809
14	House	33.39479029	-111.6379159
15	House	33.39550996	-111.6378944
16	House	33.39485803	-111.6373892
17	House	33.39546462	-111.6372463
18	House	33.39472535	-111.636848
19	House	33.39546574	-111.6368678
20	House	33.39548533	-111.6361902
21	House	33.39485075	-111.6357628
22	House	33.39552592	-111.6358855
23	House	33.39544446	-111.6351294
24	House	33.39484823	-111.6352692
25	House	33.39472339	-111.6345866
26	House	33.39549961	-111.6347103
27	House	33.39487203	-111.6341903
28	House	33.39367816	-111.6389331
29	House	33.39377837	-111.638329
30	House	33.39381056	-111.6378854
31	House	33.39373134	-111.637304
32	House	33.39665818	-111.6405585
33	House	33.39666378	-111.6400067
34	House	33.39667218	-111.6394461
35	House	33.3966621	-111.6389254
36	House	33.3966621	-111.6384657
37	House	33.39669457	-111.6379032
38	House	33.39671557	-111.6373634
39	House	33.39669513	-111.6368226
40	House	33.39668282	-111.6363186
41	House	33.39668954	-111.635803
42	House	33.39668478	-111.6352773
Sheet1

residential 74 - exported from TH.xlsx
	A	B	C
1	Type	Latitude	Longitude
2		33.39649178	-111.6410622
3		33.39648566	-111.6339996
4		33.39648616	-111.6352743
5		33.39470048	-111.6410661
6		33.39566599	-111.6410639
7		33.39470271	-111.6405401
8		33.39566621	-111.6405401
9		33.39470114	-111.6400118
10		33.39566547	-111.6400147
11		33.39470107	-111.6394848
12		33.3956636	-111.6389577
13		33.39470108	-111.6389275
14		33.39566453	-111.6384313
15		33.39470024	-111.6384002
16		33.39470024	-111.6379068
17		33.39566437	-111.6379057
18		33.39469984	-111.6373837
19		33.39566324	-111.6373828
20		33.39469892	-111.6368586
21		33.3956629	-111.6368572
22		33.39469855	-111.6363304
23		33.39566281	-111.6363279
24		33.39469844	-111.6358026
25		33.39566212	-111.6358018
26		33.39469746	-111.6352749
27		33.39566192	-111.6352786
28		33.39469739	-111.6347495
29		33.39566098	-111.6347549
30		33.39469737	-111.6342268
31		33.39490662	-111.6332597
32		33.39490714	-111.6337459
33		33.39387604	-111.6408059
34		33.39387341	-111.6389652
35		33.3938736	-111.6384388
36		33.39387212	-111.6379113
37		33.39387244	-111.6373889
38		33.39388985	-111.6352764
39		33.39388854	-111.6347493
40		33.39649167	-111.6405385
41		33.3964911	-111.6400119
42		33.39649068	-111.639484
43		33.39649109	-111.6389568
44		33.39648944	-111.6384298
45		33.3964885	-111.6379036
46		33.39648883	-111.6373802
47		33.39648829	-111.6368538
48		33.39648692	-111.6363243
49		33.39648721	-111.6357978
Sheet2

Note that the data in the Lat/Lon may be longer than what is actually shown in the cell (however if you click on the cell, you will see the entire coordinate). Also, there are more rows in Sheet2 than Sheet1, but that is OK for my purposes.

Thank you again - I appreciate any help! I am going to continue to plug away at it, but will update if I get it working the mean time!

 

[RasGhul]

I've numbered the Houses to reference the return values, does this give the intended results?

Book1
	A	B	C
1	Location type	Latitude	Longitude
2	House1	33.39484879	-111.6363394
3	House2	33.39493249	-111.6410672
4	House3	33.3953966	-111.6411081
5	House4	33.39492689	-111.6405243
6	House5	33.39543915	-111.6404992
7	House6	33.39486475	-111.6399115
8	House7	33.39550353	-111.6399654
9	House8	33.39484459	-111.6395165
10	House9	33.3954537	-111.6389164
11	House10	33.39485635	-111.6388211
12	House11	33.39543551	-111.6385087
13	House12	33.39491961	-111.6383809
14	House13	33.39479029	-111.6379159
15	House14	33.39550996	-111.6378944
16	House15	33.39485803	-111.6373892
17	House16	33.39546462	-111.6372463
18	House17	33.39472535	-111.636848
19	House18	33.39546574	-111.6368678
20	House19	33.39548533	-111.6361902
21	House20	33.39485075	-111.6357628
22	House21	33.39552592	-111.6358855
23	House22	33.39544446	-111.6351294
24	House23	33.39484823	-111.6352692
25	House24	33.39472339	-111.6345866
26	House25	33.39549961	-111.6347103
27	House26	33.39487203	-111.6341903
28	House27	33.39367816	-111.6389331
29	House28	33.39377837	-111.638329
30	House29	33.39381056	-111.6378854
31	House30	33.39373134	-111.637304
32	House31	33.39665818	-111.6405585
33	House32	33.39666378	-111.6400067
34	House33	33.39667218	-111.6394461
35	House34	33.3966621	-111.6389254
36	House35	33.3966621	-111.6384657
37	House36	33.39669457	-111.6379032
38	House37	33.39671557	-111.6373634
39	House38	33.39669513	-111.6368226
40	House39	33.39668282	-111.6363186
41	House40	33.39668954	-111.635803
42	House41	33.39668478	-111.6352773
Sheet1

Book1
	A	B	C
1	Type	Latitude	Longitude
2		33.39649178	-111.641062
3		33.39648566	-111.634
4	House41	33.39648616	-111.635274
5	House2	33.39470048	-111.641066
6		33.39566599	-111.641064
7	House4	33.39470271	-111.64054
8		33.39566621	-111.64054
9		33.39470114	-111.640012
10		33.39566547	-111.640015
11		33.39470107	-111.639485
12	House9	33.3956636	-111.638958
13		33.39470108	-111.638928
14		33.39566453	-111.638431
15		33.39470024	-111.6384
16	House13	33.39470024	-111.637907
17		33.39566437	-111.637906
18	House15	33.39469984	-111.637384
19		33.39566324	-111.637383
20	House17	33.39469892	-111.636859
21	House18	33.3956629	-111.636857
22	House1	33.39469855	-111.63633
Sheet2

Cell Formulas
Range	Formula
A2:A22	A2	=IFNA(INDEX(Sheet1!$A$2:$A$42,MATCH((LEFT(B2,6)&LEFT(C2,9)),INDEX(LEFT(Sheet1!$B$2:$B$42,6)&LEFT(Sheet1!$C$2:$C$42,9),),0)),"")

 

[rrands1]

Hi, @RasGhul - let me go take a poke at it - I see I left out an important piece, and I am sorry for that - I need to do this via VBA. Does that change things at all?

I *think* I have determined that the issue might be that my range coming back is numeric, however adding the "*" to the value to compare against (in order to get the partial match) is making it Text. However, I have been unable to get the Range statement in VBA to treat the range as text. What I am trying:
Sub test()
sLat = Left(ActiveSheet.Range("B2").Value, 6) & "*"
Rng = ThisWorkbook.Worksheets("Sheet2").Range("B2:B49")
ActiveSheet.Range("E48").Value = Application.Match(sLat, Rng, 0) '' this always comes back as "N/A", meaning it didn't find anything, though it should have...
End Sub



Thank you again - let me go try your answer as well & see if it gives me any clues! I appreciate the help!!

-randy

 

[bebo021999]

What type should be taken if, there were duplicate in Lat & Long? For instant, row 7 & 9 sheet 1:
33.39486 - 111.64 ===> to be 33.394 - 111.640
33.39484 - 111.64 ===> to be 33.394 - 111.640

Do you want to take full Lat to get the different, in case 6 left digits is duplicated?

 

[bebo021999]

Regardless to duplicate,
Intead of left, since Lat and Long is value, try using rounddown like this
for instant, first row of sheet1: 33.3948487933922 -111.636339426041 to become 33.394-111.636

Sub test()
Dim Jarr As Variant
Dim i, Lr As Long
Lr = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
ReDim Jarr(1 To Lr - 1, 1 To 2)
With WorksheetFunction
For Each cell In Sheets("Sheet1").Range("B2:B" & Lr)
    i = i + 1
    Jarr(i, 1) = .RoundDown(cell, 3) & .RoundDown(cell.Offset(, 1), 3)
    Jarr(i, 2) = cell.Offset(, -1)
Next
    For Each cell In Sheets("Sheet2").Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        For i = 1 To UBound(Jarr)
            If .RoundDown(cell, 3) & .RoundDown(cell.Offset(, 1), 3) = Jarr(i, 1) Then
                cell.Offset(, -1).Value = Jarr(i, 2)
                Exit For
            End If
        Next
    Next
End With
End Sub

 

[rrands1]

Regardless to duplicate,

Intead of left, since Lat and Long is value, try using rounddown like this
for instant, first row of sheet1: 33.3948487933922 -111.636339426041 to become 33.394-111.636

Sub test()
Dim Jarr As Variant
Dim i, Lr As Long
Lr = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
ReDim Jarr(1 To Lr - 1, 1 To 2)
With WorksheetFunction
For Each cell In Sheets("Sheet1").Range("B2:B" & Lr)
    i = i + 1
    Jarr(i, 1) = .RoundDown(cell, 3) & .RoundDown(cell.Offset(, 1), 3)
    Jarr(i, 2) = cell.Offset(, -1)
Next
    For Each cell In Sheets("Sheet2").Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        For i = 1 To UBound(Jarr)
            If .RoundDown(cell, 3) & .RoundDown(cell.Offset(, 1), 3) = Jarr(i, 1) Then
                cell.Offset(, -1).Value = Jarr(i, 2)
                Exit For
            End If
        Next
    Next
End With
End Sub

Thank you, @bebo021999 and @RasGhul - I didn't realize that I would get multiple hits doing it that way, as I assumed that I had enough digits in the geocode to make them unique enough for my purposes.. So, I think I will try and do some address matching, and I have been giving that a go for a while, but am stuck on something - rather than add to this, I think I will open a new post so it is (hopefully) clear. I very much appreciate your time and input - thank you!

-randy