Blessy Clara
Board Regular
- Joined
- Mar 28, 2010
- Messages
- 204
- Office Version
- 365
- Platform
- Windows
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
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