Transpose Contacts

**woogster** · 01-10-2011, 05:43 PM

I have a list of contacts in excel (several thousand) that appear vertically as follows:

Name
Address
Suite#
City, State Zip
Email Tel <-----same cell
Fax

Is there any way to easily transpose this information so it all appears horizontally with email and telephone in different cells?

**MarvinP** · 01-10-2011, 05:49 PM

Hi woogster,

Easy is always a relative term. If they are always in sets of 6 rows it is easier. We need a sample of your data to help with some formulas. To attach a sample file, click on Go Advanced and then the Paperclip icon above the message area.

**woogster** · 01-10-2011, 06:03 PM

They range from 5-7 rows depending on the information available. Although it doesn't always have a fax number, Fax: always appears to be the last line in the series.

Attached is a sample.

**MarvinP** · 01-10-2011, 07:04 PM

See my attached file with formulas on line 2 to grab the correct data from your format. The problem really comes when it is 5 to 7 rows. This is a killer.

It will take you some work to either make all the data look the same, that is all be in 7 rows and be consistant or change the formulas around to grab what is appropriate.

If/After you get all the data into columns, you'd do a copy and paste "Values Only" to another sheet. Then sort the data with all the blank rows going to the bottom, and you have it.

hope that all makes sense.