Delete String If Found Matching String

[muhammad susanto]

hi...

in the same line/row....

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]COL. E /ROW[/TH]
[TH]COL.F / ADDRESS[/TH]
[TH]COL. G / REGION[/TH]
[TH]COL. H / AFTER FORMULA[/TH]
[/TR]
[TR]
[TD]2[/TD]
[TD]JL. HANG LEKIU NO 29 SUKAMULIA SAIL[/TD]
[TD]SAIL[/TD]
[TD]JL. HANG LEKIU NO 29 SUKAMULIA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JL. SAOMATI RT 03/07, SAIL/TENAYAN RAYA[/TD]
[TD]TENAYAN RAYA[/TD]
[TD]JL. SAOMATI RT 03/07, SAIL/[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]JL. BELANAK IV/452 LIMBUNGAN RUMBAI PESISIR[/TD]
[TD]RUMBAI PESISIR[/TD]
[TD]JL. BELANAK IV/452 LIMBUNGAN[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]JL. LANGSAT NO.11[/TD]
[TD]SUKAJADI[/TD]
[TD]JL. LANGSAT NO.11[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]JL. KIJANG NO 24 HARJOSARI/SUKAJADI[/TD]
[TD]SUKAJADI[/TD]
[TD]ETC…[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]JL. HANG TUAH KP. KELAPA NO 38 SAIL[/TD]
[TD]SAIL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]JL. KEMUNING GG MESJID NO 30A SENAPELAN[/TD]
[TD]SENAPELAN[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]JL. GRIYA TIKA UTAMA SAI MINTAN BLOK N. NO.15 BKT.RAYA[/TD]
[TD]BUKIT RAYA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]JL. BUNGA HARUM 7 HARJOSARI/SUKAJADI[/TD]
[TD]SUKAJADI[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]JL. JEND.SUDIRMAN. ATAS SIMP.TIGA/B. RAYA[/TD]
[TD]BUKIT RAYA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]JL. PESISIR NO 127 MERANTI PANDAK RUMBAI PESISIR[/TD]
[TD]RUMBAI PESISIR[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]JL. MUSLIMIN TANAH DATAR/PKB KOTA[/TD]
[TD]PEKANBARU KOTA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]JL. PINUS GG.SUNGKAI NO 1 TANGKERANG UTARA[/TD]
[TD]BUKIT RAYA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]JL. LEMBAH MULIA NO.01 SUKAMAJU/SAIL[/TD]
[TD]SAIL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]JL. SINGKAWANG NO 12 C[/TD]
[TD]SAIL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]JL. DURIAN NO 93 PEKANBARU[/TD]
[TD]SUKAJADI[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]JL. KARTIKA UJUNG UMBAN SARI RUMBAI[/TD]
[TD]RUMBAI[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]JL. TASKURUN NO.34 WONOREJO/MARPOYAN DAMAI[/TD]
[TD]MARPOYAN DAMAI[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]JL. RAWAWIRI NO.9 TKG.LABUAI[/TD]
[TD]BUKIT RAYA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]JL. KAPAS NO 4 BUKIT RAYA[/TD]
[TD]BUKIT RAYA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]JL. KEMBANG SARI NO 18 SAIL[/TD]
[TD]SAIL[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

if the string match, delete duplicate string and display not duplicate...
how to figure it out?

note : red font i hope the clue can explain..

thanks in advance..

m.susanto

 

[mole999]

This works

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,"BUKIT RAYA",""),"MARPOYAN DAMAI",""),"PEKANBARU KOTA",""),"RUMBAI",""),"PESISIR",""),"SAIL",""),"SENAPELAN",""),"SUKAJADI",""),"TENAYAN RAYA",""))

 

[pgc01]

Hi

Try:

=TRIM(SUBSTITUTE(F2;G2;""))

Copy down

 

[muhammad susanto]

hi guys...you are really great!!!
FANTASTIC!!!

thank you very much....