Transpose not working rows to columns


D

Delilah

Hi, I have a spreadsheet with many rows of data and 2 columns of data.
Format is like this with the row names differing at times between contacts:

Last Name Smith
First Name A.
Middle Name B.
Job Title Boss
Company ABC Company
Business Street 123 Main St
Business City Anywhere
Business State CA
Business Postal Code 90888
Business Phone (555) 123-4567
Business Fax (555) 123-4576
E-mail Address (e-mail address removed)
Notes no notes right now

Last Name Jones
First Name R.
Middle Name U.
Job Title Sr. Boss
Company XYZ Company
Business Street 987 East St
Business City Wheresthat
Business State CA
Business Postal Code 98009
Business Phone (555) 123-0987
Business Fax (555) 765-1234
E-mail Address (e-mail address removed)

I've tried using transpose but when I select the rows of data, Excel put all
of the data in the first two rows (what appears on the left of above example
are all headings in the first row, what appears on the right of the above
example all appear on the same row but they are different contacts that
should be on separate rows), it does give column headings but I want row A1
across to be the column headings from the rows, and rows B2 on down to be the
data.
Thanks for any help.
 
Ad

Advertisements

R

RagDyer

Let's say that your 2 column datalist is on Sheet1, from A1 to Bn, and you
want to create your newly configured list on Sheet2.

First, on Sheet1, select A1 to A13.
Right click in the selection and choose "Copy".

Navigate to Sheet2, and right click in A1, and choose "Paste Special".
Click on "Transpose", then <OK>.

This will give you your column headers.

Now in A2 of Sheet2, enter this formula:

=INDEX(Sheet1!$B:$B,14*ROWS($1:1)+COLUMNS($A:A)-14)

Copy this formula across 14 columns to N2 (Column N will represent the blank
row between contacts).

Then, copy the 14 column selection (A2 to N2) down as far as needed.

This should give you the type of display you're looking for.
 
D

Delilah

Thank you, that worked very well. You do have to make sure all of the rows
are consistent for each contact i.e. First Name, Middle Name, Last Name,
Suffix, Title, etc. When they are consistent, this formula works perfectly.
 
Ad

Advertisements

R

RagDyer

Yes ... a consistent data source contributes immensely to everything
performing as planned.

Appreciate the feed-back.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top