Transposeing data

[Minkx]

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

 

[Ron Coderre]

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)

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

 

[Alan Beban]

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

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then if
your data is as regular as you illustrated you can put your headers in
b1:e1 and array enter into b2:b7 (for example--assuming your data is in
a2:a30)

=ArrayReshape(a2:a31,Countif(a2:a31,""),5)

Alan Beban