Found it =TRANSPOSE($A$2:$C$2)
There is also a feature that can COPY / PASTE


Thanks anyway,

Minkx

"Ron Coderre" wrote in message ...
With your posted data structure in cells A1:A20

Try this:
C1: RowRef
D1: Name
E1: Address
F1: City
G1: State
Note: Those cells contain the actuall data prefixes from Col_A

Put this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
instead of just ENTER, in cell
C1:
=SMALL((LEFT($A$1:$A$20,LEN($D$1))=$D$1)*ROW($A$1: $A$20),ROWS($1:1)+COUNTIF($A$1:$A$20,"<"&$D$1&"*" ))

Copy C1 into C2 and down until it returns #NUM! errors

Put this regular formula in cell
D2: =MID(INDEX($A$1:$A$20,$C2+COLUMNS($C:C)-1),LEN(D$1)+3,255)

Copy that formula accross through G2 and down as many rows as you need.

Using your sample data, these were my results:
RowRef_Name____Address_____City_______State
1______John____123 Elm St__Anywhere___AZ
6______Sally___555 Oak St__Anywhere___AZ

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Minkx" wrote in message
...
My data looks like this

Name: John
Address: 123 Elm St
City: Anywhere
State: AZ

Name: Sally
Address: 555 Oak St
City: Anywhere
State: AZ

How can I transpose/convert it to a table having the header in row1 and the
data beneath
TIA,
Marc