MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formating data


Posted by Travis on January 25, 2002 3:11 AM

I am trying to format some text into separate columns. The text is in one column as follows:

Column A
Company Name A
PO BOX 1
NEWARK NJ 07101

Company Name B
PO BOX 2
1400 SANS SOUCI PARKWAY
WILKES-BARRE PA 18703

Company Name C
PO BOX 3
CHICAGO IL 60694

Company Name D
PO BOX 4
CLEARFIELD UT 84015

Company Name E
PO BOX 5
OGDEN UT 84401

Company Name F
PO BOX 6
BALTIMORE MD 21264-4351

When I formatted the test to excel I made this column as illustrated above.

I would like to know of a formula to which I can make the data appear on one row rather than in multiple rows for on company the formula should result if giving me that following in one row but in different columns:

All on the same ROW

Column A
Company Name A

Column B
PO BOX 1

Column C
NEWARK NJ 07101

I have use vlookups and hlookups and pivot tables but I can’t figure out how to get the date if a row-by-row way.

I have been able to get company names to appear row-by-row in one column by sorting and I figured that I could use a combination of a vlookup with a hlookup to give me the results I was looking for but I do not know the proper formula to nest in the ether lookup. I’v also tested some DSUM function but none are working for me? So Mr Excel please let me know what can be done?


Posted by malcolm stewart on January 25, 2002 5:21 AM

Without writing a macro the easiest way (although it's lots of clicking) is to copy the block:
Column A
Company Name A
PO BOX 1
NEWARK NJ 07101
Then Edit / Paste special / Transpose (the bottom right hand box).
This then turns your row block into the columns.