Classify and Assign

Blessy Clara

Board Regular
Joined
Mar 28, 2010
Messages
201
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
If a formula approach is acceptable (& only checking 1 or 2 word city names as suggested), then possibly this.


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



Book1
AB
2Somewhere in Adelong1
3High Avenue Anna Bay2
414 Blake Rd Abermain1
5Smith St Apple CityNot Found
6Big Highway Adaminaby1
728 Main Street Agnes Banks2
Address
Cell Formulas
RangeFormula
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:
Code:
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.
 
Last edited:
Upvote 0
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



 
Last edited:
Upvote 0


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.
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top