need help with ms excel (possib macros)

[laskuh]

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

i have data in an excel sheet. its in a matrix and reads from left t
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 exce
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 anothe
cell, etc, but i have 4 new data files a day and thousands of dat
points per file. so, i need some sort of formula or a macro that can d
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

 

[GaryDK]

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

 

[Bob Phillips]

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)

 

[Max]

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)