Offset function with inconsisent data location

[debinnyc]

I have been trying to use the offset function, but I guess I don't understand
the functionality enough to customize, so here is the issue. I have one
column of data which contains a firm name, address, city, state, zip. I want
to move that data from the rows to new columns with those titles, so the data
can be used more effectively.The problem is the data is incomplete, so it
looks like this:
Engineer
John Smith
<blank>
<blank>
Jane Smith
<blank>
<blank>
<blank>
<blank>
John Doe
123 Main St
Anywhere, TX 78101

I need to get to:
Col1 Col2 Col 3
Engineer Address City,State,Zip

I am having difficulty since there is no consistency to the number of blank
rows in the original column, but I cannot simply delete them since there is
data in those rows in other columns. Does that make sense? Please help,
thanks.

 

[Sean Timmons]

Is there anything there to help determine where the names end and addresses
begin? Is it always in the 10th and 11th rows?

I could imagine you'd be able to put in cell B2 =IF(A2>"",A10,"") and in C2,
=IF(A2>"",A11,"")

can copy the formula from the formula bar and paste individually down to B9
and C9, then copy and paste the range B2:C9 and paste down yoru rows.

but, this does depend upon the address always being in the 10th and 11th
rows of an 11 row set. If the data is more strewn than that, you'll need some
sort of key in another column that can be referred to at least.