Delete Duplicate Between Two Columns - Criteria from Right

[Blessy Clara]

Hello - Please help me delete Data between Columns

I have two Columns of Data

I wanna delete the Data (repetitive between Columns)
To Note - the term may occur mutliple times in address as in Example Number 4, 7 - Search from Right and delete Common data occurring first from right

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl69, width: 314"]Po Box 197 Bassendean[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]BASSENDEAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Po Box 197 [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]15 Storey St Oak Flats[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Oak Flats[/TD]
[TD]15 Storey St[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]232 Brokers Rd Mount Pleasant[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mount Pleasant[/TD]
[TD]232 Brokers Rd[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]494 - 496 Earlville Rd Earlville[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Earlville[/TD]
[TD]494 - 496 Earlville Rd [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]622 Wickham Street Fortitude Valley[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fortitude Valley[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]622 Wickham Street[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]1st Floor Burswood House 90 Burswood Road [TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl70, width: 98"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Burswood[/TD]
[TD]1st Floor Burswood House 90 Burswood Road (Nothing to delete here)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]Adelaide Centre Ground Floor, 81 Flinders Street Adelaide[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Adelaide[/TD]
[TD][TABLE="width: 314"]
<tbody>[TR]
[TD="class: xl70, width: 314"]Adelaide Centre Ground Floor, 81 Flinders Street [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your time and efforts-

 

[Marcelo Branco]

Maybe this...

D2 copied copied down
=IF(ISNUMBER(SEARCH(C2&"|",B2&"|")),SUBSTITUTE(PROPER(B2),PROPER(C2),"",(LEN(B2)-LEN(SUBSTITUTE(PROPER(B2),PROPER(C2),"")))/LEN(C2)),B2)

Hope this helps

M.

 

[Blessy Clara]

Hi Marcelo Branco

Thank you very very much for that formula, Yes it worked super cool :D

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.