Match Tables and String Data

[Blessy Clara]

I have two Tables in two sheets:

Table 1 contains the list of all cities

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Pakenham[/TD]
[/TR]
[TR]
[TD]Point Cook[/TD]
[/TR]
[TR]
[TD]Ocean Grove[/TD]
[/TR]
[TR]
[TD]Melbourne[/TD]
[/TR]
[TR]
[TD]Dianella[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 Contains the Data - the string from which the City has to be separated from address
To Note: The city Name may be a single or two space ex - Mildura or Glen Iris - So space separation from right not possible
Address
[TABLE="width: 500"]
<tbody>[TR]
[TD]22 Alma Road, St Kilda[/TD]
[/TR]
[TR]
[TD]22 Carrington Drive Pakenham[/TD]
[/TR]
[TR]
[TD]270 Sanctuary Lakes south BVD Point Cook[/TD]
[/TR]
[TR]
[TD]PO Box 366 Ocean Grove[/TD]
[/TR]
[TR]
[TD]Po Box 411 Heidelberg[/TD]
[/TR]
[TR]
[TD]PO BOX 421, Vermont[/TD]
[/TR]
[TR]
[TD]PO Box 5134 MELBOURNE[/TD]
[/TR]
</tbody>[/TABLE]


Requirement - Separate City from address using city reference from table 1-city (Case insensitive to note, as data has a mix of cases)

[TABLE="width: 500"]
<tbody>[TR]
[TD]22 Alma Road [/TD]
[TD]St Kilda[/TD]
[/TR]
[TR]
[TD]22 Carrington Drive [/TD]
[TD]Pakenham[/TD]
[/TR]
[TR]
[TD]270 Sanctuary Lakes south BVD [/TD]
[TD]Point Cook[/TD]
[/TR]
[TR]
[TD]PO Box 366 [/TD]
[TD]Ocean Grove[/TD]
[/TR]
[TR]
[TD]Po Box 411 [/TD]
[TD]Heidelberg[/TD]
[/TR]
[TR]
[TD]PO BOX 421 [/TD]
[TD]Vermont[/TD]
[/TR]
[TR]
[TD]PO Box 5134 [/TD]
[TD]MELBOURNE[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for taking a moment to read, and further help

 

[MickG]

Try this :-
Table1 in Sheet1 column "A" Cities start "A2"
Table2 in Sheet2 column "A" Data starts "A2"
Results in sheet2 columns "A & B"

[COLOR="Navy"]Sub[/COLOR] MG29Aug28
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rng1
        [COLOR="Navy"]If[/COLOR] InStr(Dn.Value, R) > 0 [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 1).Value = Mid(Dn.Value, InStr(Dn.Value, R), Len(Dn.Value) - InStr(Dn.Value, R) + 1)
            Dn.Value = Mid(Dn.Value, 1, InStr(Dn.Value, R) - 2)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Blessy Clara]

Hi MickG,

Thank you very much for the code - i am sorry i had missed out an important point, the string may have multiple occurrences of the term, only separate at the first occurrence from Right
Example
21 max centre New Port Road New Port
8/209 Prospect Road Prospect East
8/25 Parramatta Rd Parramatta

The Term occurring first from right is the city, and this only has to be separated - Is this Possible

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG30Aug24
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, R [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] t
[COLOR="Navy"]With[/COLOR] Sheets("Sheet8")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet9")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rng1
        [COLOR="Navy"]If[/COLOR] InStr(Dn.Value, R) > 0 [COLOR="Navy"]Then[/COLOR]
            Num = InStr(Dn.Value, R)
            [COLOR="Navy"]If[/COLOR] InStr(Num + 5, Dn.Value, R, vbTextCompare) > 0 [COLOR="Navy"]Then[/COLOR]
               t = InStr(Num + 5, Dn.Value, R, vbTextCompare)
                Dn.Offset(, 1).Value = Mid(Dn.Value, InStr(Num + 5, Dn.Value, R, vbTextCompare), Len(Dn.Value) - InStr(Num + 5, Dn.Value, R, vbTextCompare) + 2)
                Dn.Value = Mid(Dn.Value, 1, InStr(Num + 5, Dn.Value, R, vbTextCompare) - 2)
            [COLOR="Navy"]Else[/COLOR]
                Dn.Offset(, 1).Value = Mid(Dn.Value, InStr(Dn.Value, R), Len(Dn.Value) - InStr(Dn.Value, R) + 1)
                Dn.Value = Mid(Dn.Value, 1, InStr(Dn.Value, R) - 2)
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] R
Num = 1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Swapnil Shah]

A slight modification , instead of using Instr you can use InstrRev function to find the position of last occurrence, Below code can address your query.

#Sub ExtractCityName()
Dim City As Range, Address As Range, CRng As Range, ARng As Range

With Sheets("Sheet1")
Set City = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

With Sheets("Sheet2")
Set Address = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

For Each ARng In Address
For Each CRng In City
If InStrRev(ARng, CRng) > 0 Then
ARng.Offset(0, 1).Value = Strings.Right(ARng, Len(ARng.Value) + 1 - InStrRev(ARng, CRng))
ARng.Value = Left(ARng, InStrRev(ARng, CRng) - 1)
End If
Next CRng
Next ARng

End Sub
#

Thanks

 

[MickG]

Good thinking "Swapnil Shah" I should have thought of that !!!

 

[Swapnil Shah]

Good thinking "Swapnil Shah" I should have thought of that !!!

Thanks a lot ..

 

[Blessy Clara]

MickG and Swapnil Shah[TABLE="width: 688"]
<tbody>[TR]
[TD="width: 688"]Thank you very very much for your time and efforts -

Given below is an example - when i run the code provided by Swapnil Shah - The First occurrence of Alice Springs which is a part of ADDRESS is also considered

the requirement is to consider the first occurrence of the term from RIGHT and split as City, please help me further understand on how to use the code if

Alice Springs Road PO Box 5296 Alice Springs[/TD]
[/TR]
</tbody>[/TABLE]

MickG code gives me [TABLE="width: 100%"]
<tbody>[TR]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]Subscript out of range (Error 9)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Swapnil Shah]

MickG and Swapnil Shah[TABLE="width: 688"]
<tbody>[TR]
[TD="width: 688"]Thank you very very much for your time and efforts -

Given below is an example - when i run the code provided by Swapnil Shah - The First occurrence of Alice Springs which is a part of ADDRESS is also considered

the requirement is to consider the first occurrence of the term from RIGHT and split as City, please help me further understand on how to use the code if

Alice Springs Road PO Box 5296 Alice Springs[/TD]
[/TR]
</tbody>[/TABLE]

MickG code gives me [TABLE="width: 100%"]
<tbody>[TR]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]Subscript out of range (Error 9)[/TD]
[/TR]
</tbody>[/TABLE]

Not sure why you have an error ,

When I checked the same code , I get output as following. Please let me know if that is not the one you intended.

Cell A2 : Alice Springs Road PO Box 5296 , Cell B2 : Alice Springs

Thanks
Swapnil Shah