Match Tables and String Data

[Blessy Clara]

I have two Tables in two sheets:

Table 1 contains the list of all cities

Table 1

Pakenham

Point Cook

Ocean Grove

Melbourne

Dianella

<tbody>
</tbody>

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

22 Alma Road, St Kilda

22 Carrington Drive Pakenham

270 Sanctuary Lakes south BVD Point Cook

PO Box 366 Ocean Grove

Po Box 411 Heidelberg

PO BOX 421, Vermont

PO Box 5134 MELBOURNE

<tbody>
</tbody>

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

22 Alma Road	St Kilda
22 Carrington Drive	Pakenham
270 Sanctuary Lakes south BVD	Point Cook
PO Box 366	Ocean Grove
Po Box 411	Heidelberg
PO BOX 421	Vermont
PO Box 5134	MELBOURNE

<tbody>
</tbody>

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

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

<tbody>
</tbody>

MickG code gives me

Subscript out of range (Error 9)

<tbody>
</tbody>

 

[Swapnil Shah]

MickG and Swapnil Shah

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

<tbody>
</tbody>

MickG code gives me

Subscript out of range (Error 9)

<tbody>
</tbody>

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