HI

I have a 64 X 96 field with data and want to bring the 96 columns in one
column so that column Nr1s 64 entries are followed by column Nr2s entries. Is
that possible from another worksheet, too?

Thanks
ANDY

With your data in A1:CR64 in Sheet1, put this in A1 in
another sheet and drag down:

=OFFSET(Sheet1!$A$1,INT(ROW()/97),MOD(ROW()-1,96),)

Or, more generally, use:

=OFFSET(INDIRECT(MID(CELL("address",rng),FIND("]",CELL
("address",rng))+1,99)),INT(ROW()/97),MOD(ROW()-1,96),)

where "rng" = your range, 96 col x 64 rows.

HTH
Jason
Atlanta, GA

-----Original Message-----
HI

I have a 64 X 96 field with data and want to bring the
96 columns in one
column so that column Nr1s 64 entries are followed by
column Nr2s entries. Is
that possible from another worksheet, too?

Thanks
ANDY
.

Here's a simple vba UDF for you
Function One_column(source As Range)
Dim output As Variant
Dim cell As Range
Dim index As Long
ReDim output(1 To source.Count)
For Each cell In source.Cells
index = index + 1
output(index) = cell.Value
Next
One_column = WorksheetFunction.Transpose(output)
End Function

to use it, select a column for output, the array enter the formula

eg, data table may range named MyData
select a column long enough for the output and type
=One_Column(mydata)
hold down shft+ctrl then press Enter to enter the formula as an array formula






"bandy2000" wrote:

HI

I have a 64 X 96 field with data and want to bring the 96 columns in one
column so that column Nr1s 64 entries are followed by column Nr2s entries. Is
that possible from another worksheet, too?

Thanks
ANDY

Hi all,
I'm not really sure if those 2 code examples are exactly what I wanted to do
but after some touble I found a solution that works for me:

Const RowNum = 64
Const ColNum = 96
Const DataStartCell = "B2"

Private Sub CommandButton1_Click()


CopyColumn "Sheet1", "Sheet1", "A79"
CopyColumn "Sheet2", "Sheet1", "B79"

End Sub

Sub CopyColumn(SourceSheetname As String, DestSheetname As String, DestRange
As String)

Dim i As Integer

For i = 1 To ColNum

Worksheets(SourceSheetname).Range(DataStartCell).O ffset(0, i -
1).Resize(RowNum, 1).Copy _
Destination:=Worksheets(DestSheetname).Range(DestR ange).Offset((i - 1) *
RowNum, 0)

Next i
End Sub


I'm not really into VBA but compared to the 2 examples I think this is
easier to understand. But again I'm not really a VBA crack. If you have any
suggestion why this would be a bad or sloppy solution please tell me.
Thanks
Andy
"Patrick Molloy" wrote:

Here's a simple vba UDF for you
Function One_column(source As Range)
Dim output As Variant
Dim cell As Range
Dim index As Long
ReDim output(1 To source.Count)
For Each cell In source.Cells
index = index + 1
output(index) = cell.Value
Next
One_column = WorksheetFunction.Transpose(output)
End Function

to use it, select a column for output, the array enter the formula

eg, data table may range named MyData
select a column long enough for the output and type
=One_Column(mydata)
hold down shft+ctrl then press Enter to enter the formula as an array formula






"bandy2000" wrote:

HI

I have a 64 X 96 field with data and want to bring the 96 columns in one
column so that column Nr1s 64 entries are followed by column Nr2s entries. Is
that possible from another worksheet, too?

Thanks
ANDY