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
A 2
A 3
A 4
B 5
B 6
B 7
B 8
C 9
C 10
C 11
C 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Saif1k" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Saif1k
> ------------------------------------------------------------------------
> Saif1k's Profile:
http://www.excelforum.com/member.php...o&userid=30242
> View this thread: http://www.excelforum.com/showthread...hreadid=499154
>

[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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Saif1k" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Saif1k
> ------------------------------------------------------------------------
> Saif1k's Profile:
http://www.excelforum.com/member.php...o&userid=30242
> View this thread: http://www.excelforum.com/showthread...hreadid=499154
>

[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.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Saif1k]

Hi All,

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

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

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

I appreciate your and Ragdyer responds and helps.

Best Regards,
Saif

[Max]

Welcome back, and thanks for your feedback.
Glad to hear that it worked for you
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Saif1k" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> Thank you very much for your help and support. Especially i need to
> thanks Max and Ragdyer.
>
> Max, i tried your answers and it works well with my requirement to
> organise the data in columns.....Thanks very much for your help and
> advise.
>
> I am sorry if i did not respond immedialtly since i was a way from the
> office for couples of days for some urgent field work...and just i red
> your answers and tried them out.
>
> I appreciate your and Ragdyer responds and helps.
>
> Best Regards,
> Saif
>
>
> --
> Saif1k
> ------------------------------------------------------------------------
> Saif1k's Profile:
http://www.excelforum.com/member.php...o&userid=30242
> View this thread: http://www.excelforum.com/showthread...hreadid=499154
>