Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
help with excel (maybe macros) | Excel Worksheet Functions | |||
How do I update Excel 2000 macros to work in Excel 2002? | Excel Discussion (Misc queries) | |||
how can i edit excel 4 macros in Excel 2003 | Excel Worksheet Functions | |||
Is there a way to sort by color in excel? using macros? | Excel Worksheet Functions |