good morning everybody!
i need your assistance please...in my sample xls,there are some contents (text) that i don't need.
i'd like to remove the duplicate entries from the Consignee Address rows based on the contents of the Consignee City cells.
what i mean? lets say, here,the word CHIOS shows 3 times,we dont need that,so we have to delete it in the Consignee Address,and leave it only in the Consignee City

Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636276 MRS NELLH NIRI LEAFOROS AIGAIIOU 22 CHIOS CHIOS 82100 CHIOS 302023288 1 0.11



so,the final text should be like this:
Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636276 MRS NELLH NIRI LEAFOROS AIGAIIOU 22 82100 CHIOS 302023288 1 0.11


in the second case,we have something like this:
Shipment Number Consignee Consignee Address Consignee ZipCode Consignee City Consignee Phone Pieces Shipment Total Weight
0866-03636453 MRS KRISTINA L. KALIVION 252 LAMIA -- SELECT STATE -- GR 35100 -- SELECT STATE -- 6954689 1 0.43

here,we want to move the city (LAMIA) ,and replace the -- SELECT STATE -- in the to Consignee City

please check the demo file,to see exactly what we need...
DEMO.xlsx

could someone help me achieve this with some macro?
thank in advice!