Rows to columns with multiple sets

[chrisL]

Hi -

I have a file of data that I need to flip rows and columns, however, there
are multiple sets of data so the "paste special>>transpose" doesn't meet my
need. So, for example, my current data file looks something like this:

A B
City Ozark
State MO
ZIP 65721
City Kansas City
State MO
ZIP 64118
City Suffolk
State VA
ZIP 23434


And I need to transform it into something like this:

City State ZIP
Ozark MO 65721
Kansas City MO 64118
Suffolk VA 23434

Is there a systematic way to accomplish this in Excel???

 

[~L]

Somewhat unsophisticated, but:
Sort by column A
Filter by State
Cut those results
Unfilter
Paste the results to the right of your data
Scroll to the zip codes, then cut them and past them to the right of your
data again.
Delete columns A, C, and E (which should contain only the words City, State,
and Zip respectively), then insert a row above your data with the appropriate
headers.