How do I convert rows into columns in Excel 2010?

Question

My spreadsheet looks like this (ignore the "dots" - they're for spacing):

...............Column 1

Row 1:.....+++++++++
Row 2:.....Name1
Row 3:.....Address1
Row 4:.....Address1
Row 5:.....+++++++++
Row 6:.....Name2
Row 7:.....Address2
Row 8:.....Address2
Row 9:.....+++++++++

And so on for about 2,000 names and addresses. This isn't how I inputted the information, rather it's how it was outputted from a computer program. I would like the spreadsheet to look like this:

...............Column 1..............Column 2.............Column 3

Row 1:.....Name1..................Address1.............Address1
Row 2:.....Name2..................Address2.............Address2

Does anybody have a good way to accomplish this? I basically want to put every row after the "++++++" in a column until we reach another "++++++".

Your help would be unbelievably appreciated.

Thanks!

Answer 1

Hi,



With your list down column A, put this in B1, copy it over to E1, then copy it down as needed. When you start to see 0's, you've gone far enough.



=INDIRECT("A" & COLUMN() - 1 + ((ROW()-1) *4))



If it's consistent with 4 bits of data, (+++++++++, Name, Address, Address) then this will work for you.

Answer 2

Using transpose doesn't work, because you would have to transpose each section of 3 rows. I don't know of one formula that will get that format for you in one swoop. This is what I would do- a little bit of work, but can in done in a few minutes.



Save your original of course

In cell B1, enter "=A1"

In cell B2, enter "=A2"

In cell C2, enter "=A3"

In cell D2, enter "=A4"



Then copy from B1 to D2 (six cells) and paste at the next location B5 (to the right of your ++++++)



Paste this several more times. Then copy a large section of these and paste. The larger section you copy, the fewer times you'll have to paste.



Then, sort by Column B and delete all unnecessary rows and Column A

Answer 3

I would copy the data from Excel into Word the convert the table to Text with paragraph marks as delimiters for each line. Then using Find and Replace, search for + signs and replace with nothing untill there are no more + signs. Then, each individual record is separated from the next record by two paragraph marks. I would replace the paragraph marks (^p^p) with (xxxx) or other text that doesn't show up in the data. The next step is to replace the remaining paragraph marks (^p) with a comma tab (^t) and finally replace the xxxx with a paragraph mark (^p). This will create a paragraph for each name with all the elements of the name and address separated by tabs and each record ending with a paragraph mark.



Copy this back into Excel. Select the data and use the Text To Columns feature of Excel and choose delimited, click next and choose Tab as the delimiter then click finish and that should be all there is to it.

Answer 4

go with each and every finished handle (one after the other) because it is above and reproduction then placed the cursor in a column to the astonishing and Paste, Transpose. this could place all the climate of the handle in a column of its very own.