Hello,
I have a large data like this in an excel sheet
A D G
B E H
C F

I want to change in it to one column like this

A
B
C
D
E
F
G
H

Any function or formula please?

Cannot recall who posted the original code to give credit where due but here
is my modified version of a sub that changes a matrix to a column.

Paste the sub to a new module and run it. Experiment first in a test
workbook to become familiar with its operation and to check that this is
what you want.

If you are inexperienced with VBA:

Press Alt/F11
Click Insert, Module
Paste the Sub at the bottom of my message.

Run it from your Macro menu.

To achieve what you wish:
Run the macro
Select the range of data to be transposed when prompted an press enter
Select the top cell where you want the column to start
Choose "no" to the question "do you wish to transpose across rows first"

In relation to the above, "yes" would result in a column ordered
a,d,g,b,e,h,c,f


Sub Matrix2Column()
Dim v As Variant
Dim nCol As Long
Dim nRow As Long
Dim rOut As Range
Dim iCol As Long

On Error Resume Next
v = Application.InputBox("Select range to copy", Type:=8).Value

If IsEmpty(v) Then Exit Sub


nRow = UBound(v, 1)
nCol = UBound(v, 2)

Set rOut = Application.InputBox("Select destination",
Type:=8).Resize(nRow, 1)
If rOut Is Nothing Then Exit Sub

Select Case MsgBox("Do you wish to transpose across rows first?",
vbYesNo Or vbExclamation Or vbSystemModal Or vbDefaultButton1,
"Row-by-row?")
Case vbYes
v = WorksheetFunction.Transpose(v)
Case vbNo

End Select

For iCol = 1 To nCol
rOut.Value = WorksheetFunction.Index(v, 0, iCol)
Set rOut = rOut.Offset(nRow)
Next iCol
End Sub

--
Steve

"Anex" wrote in message
...
Hello,
I have a large data like this in an excel sheet
A D G
B E H
C F

I want to change in it to one column like this

A
B
C
D
E
F
G
H

Any function or formula please?

hi
this might work for you. you said you had a large amount of data but gave a
small example. the code below assume that you have a solid block of data and
may not work as expected if you have blank cells mixed in with the data. if
so post back.
code assumes a header for each column.
make a backup copy before runing unfamilar code. (always)
Sub transposeit()
Dim r As Range
Dim ro As Range
Set r = Range("B2")
Do While Not IsEmpty(r)
Set ro = r.Offset(0, 1)
Range(r, r.End(xlDown)).Copy Destination:= _
Range("A2").End(xlDown).Offset(1, 0)
Range(r, r.End(xlDown)).ClearContents
Set r = ro
Loop
End Sub

regards
FSt1

"Anex" wrote:

Hello,
I have a large data like this in an excel sheet
A D G
B E H
C F

I want to change in it to one column like this

A
B
C
D
E
F
G
H

Any function or formula please?

One easy formulas play to tinker with ..

If the source data as posted is in cols A across within the 1st 3 rows,
with the top left cell in A1

Place this in any startcell below the source data,
say in B5:
=OFFSET(A$1,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3))
then just copy B5 down as far as required to extract it as desired

Adapt the anchor (the "A$1") and the number of rows (the "3") to suit
Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Anex" wrote:
I have a large data like this in an excel sheet
A D G
B E H
C F

I want to change in it to one column like this

A
B
C
D
E
F
G
H

Any function or formula please?