Converting Rows into colums(Urgent)

[Saif1k]

Hi All,

I have problem to convert data from Rows to colums but i do not want to
use Transpose option because i need to do it one by one. For example:

A 1 2 3 4
B 5 6 7 8
C 9 10 11 12

I need to convert it into columns in one go..like following:
A 1
2
3
4
B 5
6
7
8
C 9
10
11
12
Any one can help me please in this issue...if you can guide me which
formula should i use...

BR// Saif

 

[Max]

One way ..

Assuming source data in cols A to E, from row1 down
with empty cols to the right

Put in F1: =A1&B1
Put in G1: =C1, copy G1 to I1
Select F1:I1, copy down until the last row of source data

Put in J1:
=OFFSET($F$1,INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4))

Put in K1:
=IF(MOD(ROWS($A$1:A1)-1,4)=0,LEFT(J1,1),J1)

Put in L1:
=IF(MOD(ROWS($A$1:A1)-1,4)=0,RIGHT(J1,1),"")

Select J1:L1, copy down as far as required to exhaust the data extract

The required results will be returned in cols K and L

Clean-up by selecting cols F to L, then do an "in-place":
Copy > Paste special > check "Values" > OK
to kill all the formulas, then delete cols F to J

 

[Max]

Just re-guessing <g> ... if for some reason, what you really want is this:
(with the source data in cols A to E, row1 down as before)

A 1
A 2
A 3
A 4
B 5
B 6
B 7
B 8
C 9
C 10
C 11
C 12

Put in F1:
=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/4),)&
OFFSET($B$1,INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4))

Copy F1 down until data is exhausted
Col F will return the required results
(Kill the formulas in col F if desired, as before)

 

[Max]

A 1

A 2
A 3
A 4

The previous formula for col F presumes you want it joined (i.e.: "A1",
"A2", etc) all in one col. If you want it split into 2 cols, just "split"
the formula, viz.:

Put in F1:
=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/4),)

Put in G1:
=OFFSET($B$1,INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4))

Then select F1:G1 and copy down

 

[Ragdyer]

I'm assuming from your example that A1 to A4 contains 1 to 4,
B1 to B4 contains 5 to 8,
And C1 to C4 contains 9 to 12.

And, you want to copy down a column, and return the numbers (cell contents)
in order.

Enter this formula anywhere, and copy down 12 rows:

=INDEX($A$1:$C$4,MOD(ROWS($1:1)-1,4)+1,ROWS($1:4)/4)

If however, I incorrectly assumed the data configuration,
And your numbers ran across the rows, so that,
A1 to D1 contains 1 to 4,
A2 to D2 contains 5 to 8
And A3 to D3 contains 9 to 12,
And, you want to copy down a column, and return the numbers (cell contents)
in order, then try this formula anywhere, and copy down 12 rows:

=INDEX($A$1:$D$3,ROWS($1:4)/4,MOD(ROWS($1:1)-1,4)+1)

 

[Max]

Saif1k,

Just re-guessing <g> ... if for some reason, what you really want is this:

Ok, I'll own up on the re-guess <g>. I happened by Excelforum,
and noticed that you had since *edited* your orig. post
which had formerly given the desired results as:

A 1
2
3
4
B 5
6
7

etc

While Excelforum may allow you to edit what's been posted earlier, it's
better not to do this, as the editing will not carry through to the
newsgroup. This means that no one out there (other than Excelforum
users/readers) will know that you have since edited your specs/reqts. If
you need to revise what you posted earlier, just write the revisions as a
reply to your orig. post. In that way, your revisions will be visible to
the many others out there who could have something to offer you.

And you should always promptly feedback to those who respond to you ..
(Hey, it's supposed to be a discussion <g>. Responders won't even know
whether you had read/tried what's suggested or whether it worked, etc, etc.
if you just keep quiet. Don't.)

 

[Saif1k]

Hi All,

Thank you very much for your help and support. Especially i need t
thanks Max and Ragdyer.

Max, i tried your answers and it works well with my requirement t
organise the data in columns.....Thanks very much for your help an
advise.

I am sorry if i did not respond immedialtly since i was a way from th
office for couples of days for some urgent field work...and just i re
your answers and tried them out.

I appreciate your and Ragdyer responds and helps.

Best Regards,
Sai

 

[Max]

Welcome back, and thanks for your feedback.
Glad to hear that it worked for you