Hi,

I have a query on some data i'm currently analysing and it's causing me a
headache. I'm hoping there is a simple solution to this that doesn't involve
macros.

Any help much appreciated

Sample data: this is what i have in one column of data at present. each name
beneath a port represents people working at that particular port. However,
what i want is in the column along side this data for it to show the port
name that each employee works at.

what i have at present is as follows;

cell a1 port:rotterdam
cell a2 richard
cell a3 david
cell a4 paul
cell a5 port:south africa
cell a6 james
cell a7 sam
cell a8 keith
cell a9 duncan
cell a10 port:port talbot
cell a11 simon
cell a12 rachel

what i'd like to see is a formula in column B to return the relevant port
for each individual (shown below) It is a large document with over 5000 rows
so a formula is a must if possible.

column A column B
Port: rotterdam
Richard rotterdam
David rotterdam
Paul rotterdam
Port: south africa
james south africa
sam south africa
keith south africa
duncan south africa
Port: port talbot
simon port talbot
rachel port talbot