transpose text from columns to rows (EXCEL)

Microsoft Excel 2010 - license
November 3, 2010 at 13:28:10
Specs: Windows XP
hi there

im trying to turn this:

Tharmalingam T
416-546-1757
73 Bonspiel Dr
Scarborough, ON M1E 5K5

Thevathas Nirmalarani
416-724-6051
70 Bonspiel Dr
Scarborough, ON M1E 5K5

.........................


into...

Tharmalingam T 416-546-1757 73 Bonspiel Dr Scarborough, ON M1E 5K5
Araya S 416-287-0384 122 Bonspiel Dr Toronto, ON M1E 5K5


each 4 rows into 4 columns
loop


See More: transpose text from columns to rows (EXCEL)

Report •


#1
November 3, 2010 at 20:43:37
In your example it appears that there a blank row between each group of 4 lines.

Is that actually the case?

P.S. I really hope that those are fake names, numbers and addresses.


Report •

#2
November 3, 2010 at 21:20:18
yes that is the case..

fours rows of data
then 1 blank line...


Report •

#3
November 4, 2010 at 08:35:04
The example in your post no longer makes sense. The data in the second line of your output doesn't match the data in the second address group.

Assuming that that is just a typo, here's a non-Macro way of getting what (I think) you want.

Assuming your data starts in A1...

In G1 and G2 enter 1 and 6:

    G
1   1
2   6

Grab the fill handle and fill down until the last value equals the first Row of your last address group.

    G
1   1
2   6
3  11
4  16
etc.

Each value in Column G should be the first Row number of each address group.

In B1 enter this formula:

=OFFSET(INDIRECT("$A"&$G1), COLUMN()-2, 0)

Drag this over to E1 then drag A1:E1 down as far as you need to to get all of your groups transposed.

Then Copy...Edit...PasteSpecial...Values to replace the formulas with the text.

As you drag the formula down, the INDIRECT function will create a reference to A1, then A6, then A11, etc.

As you drag the formula across, the COLUMN()-2 will create the rows argument (1, 2, 3, 4) for the OFFSET function. The cols argument will always be 0.


Report •

Related Solutions


Ask Question