Classify and Assign

[Blessy Clara]

Hi,

I would like to classify the CITY in an unstructure Address Column

in Sheet1 - i have the City table - with ALL CITY NAMES (standard reference table)
in Sheet 2 - I have the data i need to parse and classify

Requirement - To compare with the city table and assign the numbers based as below
Type = Two alphabet or 1 letter alphabet

SINGLE Alphabet= Reynella, Sydney
TWO Alphabet= Bella Vista, Wagga Wagga, Mt Albert, Caroline Springs

TO NOTE the term may occur multiple times in an address as

Caroline Springs Plaza Caroline Springs

ONLY COUNT/CONFIRM FROM RIGHT
It is an unstructured data and hence punctuation is inconsistent



CITY TABLE
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 298"]
<tbody>[TR]
[TD="class: xl63, width: 298"]Aarons Pass[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR]
[TD="class: xl65, width: 298"]Abbotsbury[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR]
[TD="class: xl65, width: 298"]Abminga Station[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR]
[TD="class: xl65, width: 298"]Currawong Beach[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR]
[TD="class: xl65, width: 298"]Reedy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR]
[TD="class: xl65, width: 298"]Reefton[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR]
[TD="class: xl65, width: 298"]Reidsdale[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


DATA TABLE

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="class: xl63, width: 406, align: left"]Yenyening Lakes Rd Bally Bally[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="width: 406, align: left"]Zurich House Bundall[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="width: 406, align: left"]Zhen Building 210/33 Lexington Drive, Bella Vista[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="class: xl65, width: 406, align: left"]68 Dixon St Wellington New Zealand Aarons Pass[TABLE="width: 406"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="class: xl65, width: 406, align: left"]60/68 Shop 5 Stockdale Crs Abbotsbury[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="class: xl65, width: 406, align: left"]60 Winzor St Reefton[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


POST CODE RUN
[TABLE="width: 500"]
<tbody>[TR]
[TD]Yenyening Lakes Rd Bally Bally[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="width: 406, align: left"]Zurich House Bundall[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="width: 406, align: left"]Zhen Building 210/33 Lexington Drive, Bella Vista[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]68 Dixon St Wellington New Zealand Aarons Pass[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]60/68 Shop 5 Stockdale Crs Abbotsbury[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="class: xl65, width: 406, align: left"]60 Winzor St Reefton[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you

 

[BrianJN1]

Is it possible to add a column beside your City names such that if there is a space that value shows as a 2 rather than a 1?
Then when comparing an address with a City name, we do an "= if RIGHT(Address,Len(City name)) equals our City Name" algorithm and so record either a 1 or a 2 in your column when found?

By the way, how lengthy is your City names list? The full Australian Postcode length?

 

[pkdeimos]

Hi,

I have tried the following approach and it worked:

Assumption:
City name appears last in the address
City name will have only 1 or 2 words

1. Trim the address to avoid unexpected results when there are additional spaces in the end
2. Extract the last word of the address using
G3=RIGHT(F3,LEN(F3)-FIND("@",SUBSTITUTE(F3," ","@",LEN(F3)-LEN(SUBSTITUTE(F3," ","")))))
3. Extract last 2 words of the address using
H3=RIGHT(F3,LEN(F3)-FIND("@",SUBSTITUTE(F3," ","@",LEN(F3)-LEN(SUBSTITUTE(F3," ",""))-1)))
4. Check values in Columns G & H are in City table
=IF(COUNTIF($B$3:$B$12,H3)>0,1,IF(COUNTIF($B$3:$B$12,I3)>0,2))

F3 has trimmed address and B3:B12 has city table.

I would like to know if there a more efficient way to do this.

Regards

 

[BrianJN1]

I've not tried, but if you say that works then terrific.

I'm thinking that our author might like those formulae made ready for a VBA run.
By that I mean that the formulae are to be inserted into cells across columns and then down over how many rows.

I can see many instances where I'd have editorial problems in rewriting when it comes to characters like: @ : , " ".

If my understanding is that our author does need a VBA solution I'd like to know the exact process to solve that.

 

[pkdeimos]

If it can be done using formula, I believe it can be done in vba. I am not well versed with vba. I would also like to know if there is a better approach. My approach worked, but it seems crude to me.

I've not tried, but if you say that works then terrific.

I'm thinking that our author might like those formulae made ready for a VBA run.
By that I mean that the formulae are to be inserted into cells across columns and then down over how many rows.

I can see many instances where I'd have editorial problems in rewriting when it comes to characters like: @ : , " ".

If my understanding is that our author does need a VBA solution I'd like to know the exact process to solve that.

 

[BrianJN1]

I understand what you are saying. There may be a more efficient way to write your formulae, I don't know.

My comment was more about how to rewrite your formulae under VBA syntax, I am very much an amateur in that field. I really sense that our author needs that for a solution.

In comment #2 I proposed a possible model which needs to be addressed. Maybe elements of your formulae might be incorporated into that model. That said, have I the model sought?

 

[Peter_SSs]

If a formula approach is acceptable (& only checking 1 or 2 word city names as suggested), then possibly this.

Book1
	A
2	Aberdare
3	Abermain
4	Adaminaby
5	Adelong
6	Agnes Banks
7	Albury
8	Anna Bay
9	Ardlethan
City

Book1
	A	B
2	Somewhere in Adelong	1
3	High Avenue Anna Bay	2
4	14 Blake Rd Abermain	1
5	Smith St Apple City	Not Found
6	Big Highway Adaminaby	1
7	28 Main Street Agnes Banks	2
Address

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),100)),City!A:A,0)),2,IF(ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),50)),City!A:A,0)),1,"Not Found"))

If a vba solution is required, then that same formula can be utilised:

Sub CheckCityNames_v1()
  Const CitySheetName As String = "City"
  Const frmla As String = "=IF(ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(TRIM(#),"" "",REPT("" "",50)),100)),'^'!A:A,0)),2," & _
                          "IF(ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(TRIM(#),"" "",REPT("" "",50)),50)),'^'!A:A,0)),1,""Not Found""))"
  
  With Sheets("Address").Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace(Replace(frmla, "#", .Address), "^", CitySheetName))
  End With
End Sub

Note:
It will be hard to be sure of being 100% accurate. Take the example addresses:
"Harold Smith St Lawrence"
"Claire Circuit St Lawrence"

Both "Lawrence" and "St Lawrence" are valid town names in Australia. We can see that the first town should be Lawrence and the second St Lawrence, but it would be hard to put logic to it, paricularly given that both addresses consist of 4 words.

 

[Blessy Clara]

Hi BrianJN1, pkdeimos and Peter_SSs

First of all thank you very much for your response and appreciate your time.

BrianJN1 - Thanks Brian, Well yes, it is the complete set of Australian Suburb List - which is close to 17000+.
Is it possible to add a column beside your City names such that if there is a space that value shows as a 2 rather than a 1? = Yes Possible to Classify in City Table as 1 or 2
comparing an address with a City name, we do an "= if RIGHT(Address,Len(City name)) equals our City Name= But does this classify whether the City is a 1 or 2
Haven't got the concept of it though.

pkdeimos - Thank you for taking time for the step by step explanation - , i tried Steps 1 to 3 -

As per the address is split at the first space from right in Col G and second Space in Col H, there after

4. Check values in Columns G & H are in City table
=IF(COUNTIF($B$3:$B$12,H3)>0,1,IF(COUNTIF($B$3:$B$12,I3)>0,2)) - Now which Col does this Formula have to be pasted
Please clarify - Also please help me understand further

Peter_SSs - Thank you very much for the formula as well as the code - My requirements were met using the formula itself. and also thank you for the vba, the additional NOT FOUND part was very thoughtful and helpful :D

 

[pkdeimos]

4. Check values in Columns G & H are in City table
=IF(COUNTIF($B$3:$B$12,H3)>0,1,IF(COUNTIF($B$3:$B$12,I3)>0,2)) - Now which Col does this Formula have to be pasted
Please clarify - Also please help me understand further

Hi Blessy,

There was a typo in Step 4 of my response. Please use this formula in I3

I3 =IF(COUNTIF($B$3:$B$12,G3)>0,1,IF(COUNTIF($B$3:$B$12,H3)>0,2))

Hope this helps.

Cheers.

 

[Peter_SSs]

Peter_SSs - Thank you very much for the formula as well as the code - My requirements were met using the formula itself. and also thank you for the vba, the additional NOT FOUND part was very thoughtful and helpful :D

Glad it worked for you.