hi there im trying to turn this:

Tharmalingam T

416-546-1757

73 Bonspiel Dr

Scarborough, ON M1E 5K5Thevathas 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

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.

yes that is the case.. fours rows of data

then 1 blank line...

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 6Grab 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

rowsargument (1, 2, 3, 4) for the OFFSET function. Thecolsargument will always be 0.

Ask Your Question

Weekly Poll