need help with ms excel (possib macros)
 

you guys are the most technical people i know, so i thought i would
bring this to you. his is my problem:

i have data in an excel sheet. its in a matrix and reads from left to
right and top to bottom, as if you were reading a book (A1, B1... A2,
B2... etc).

A B C D E
1 . . . . .
2 . . . . .
3 . . . . .

i need to somehow transfer this to another part of the sheet in excel
and have all my data in one column:

A1
B1
C1
D1
E1
A2

i know i can do this by just making a certain cell equal to another
cell, etc, but i have 4 new data files a day and thousands of data
points per file. so, i need some sort of formula or a macro that can do
this, but im not quite sure how to get this done.

can anyone help? or refer me to someone that might be able to?

i hope this makes sense.


--
laskuh

Hi laskuh,

Try this macro and see if it does what you want for one of your data
files. It should transfer your data as described, starting in cell A2 -

Sub MoveDataToColumn()
Dim icol As Integer
Dim lrow As Long
Dim rngCopy As Range
Dim rngPaste As Range

Application.ScreenUpdating = False
icol = 2
Columns(1).Insert Shift:=xlToRight

For lrow = 1 To Cells(Rows.Count, icol).End(xlUp).Row
Set rngCopy = Range(Cells(lrow, icol), _
Cells(lrow, icol).End(xlToRight))
Set rngPaste = Cells(Rows.Count, icol - 1).End(xlUp).Offset(1, 0)

' if the next data record will fill oe exceed the column rows
If rngPaste.Row + rngCopy.Cells.Count = Rows.Count Then
' insert a new column and start at row 2 again
Columns(icol).Insert Shift:=xlToRight
icol = icol + 1
Set rngPaste = Cells(Rows.Count, icol - 1).End(xlUp).Offset(1, 0)
End If

rngCopy.Copy
rngPaste.PasteSpecial Transpose:=True
rngCopy.ClearContents
Next lrow

Application.CutCopyMode = False
Cells(1, 1).Select
End Sub

Regards,

Gary

Sub Test()
Dim cLastRow As Long
Dim cLastCol As Long
Dim i As Long
Dim j As Long

cLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
For j = 1 To cLastCol
Cells((i - 1) * cLastCol + j, "A").Value = Cells(i, j).Value
If i = 1 And j = 1 Then
'
Else
Cells(i, j).Value = ""
End If
Next j
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"laskuh" wrote in message
...

you guys are the most technical people i know, so i thought i would
bring this to you. his is my problem:

i have data in an excel sheet. its in a matrix and reads from left to
right and top to bottom, as if you were reading a book (A1, B1... A2,
B2... etc).

A B C D E
1 . . . . .
2 . . . . .
3 . . . . .

i need to somehow transfer this to another part of the sheet in excel
and have all my data in one column:

A1
B1
C1
D1
E1
A2

i know i can do this by just making a certain cell equal to another
cell, etc, but i have 4 new data files a day and thousands of data
points per file. so, i need some sort of formula or a macro that can do
this, but im not quite sure how to get this done.

can anyone help? or refer me to someone that might be able to?

i hope this makes sense.


--
laskuh

Another way to strip the matrix data into one column ..

With source data in cols A to E, in row1 down as posted,

Put in say, K1:

=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/5),MOD(ROWS($A$1:A1)-1,5))

Copy K1 down until zeros appear, signalling exhaustion of data

Just adjust the "5" within the INT(...) and MOD(...) parts
to suit the number of cols in the source
(above is for cols A to E, i.e. 5 cols)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"laskuh" wrote in message
...

you guys are the most technical people i know, so i thought i would
bring this to you. his is my problem:

i have data in an excel sheet. its in a matrix and reads from left to
right and top to bottom, as if you were reading a book (A1, B1... A2,
B2... etc).

A B C D E
1 . . . . .
2 . . . . .
3 . . . . .

i need to somehow transfer this to another part of the sheet in excel
and have all my data in one column:

A1
B1
C1
D1
E1
A2

i know i can do this by just making a certain cell equal to another
cell, etc, but i have 4 new data files a day and thousands of data
points per file. so, i need some sort of formula or a macro that can do
this, but im not quite sure how to get this done.

can anyone help? or refer me to someone that might be able to?

i hope this makes sense.


--
laskuh