EXCEL Macro - HELP! Please -
I thought it would be easy but I must be missing something?
I'll gladly send someone a $50 gift certificate from Amazon.com if I use your solution. I'll also acknowledge you here.
1. I have a huge array 3 columns by well over 14,000 rows.
2. The array actually is a collection of a large number of smaller arrays of 3 columns by 14 rows.
3. Each cell in the smaller array represents a specific type of information - e.g. the cell in the top left is a date, the cell to its immediate right is a time. it looks like this
A B C
1 1 2 3
2 4 5 6
. . . .
. . . .
. . . .
13 37 38 39
14 40 41 42
15 1 2 3
and so on to well over 15,000 rows
If my large array actually had exactly 14,000 rows, there would be exactly 1,000 of the smaller arrays stacked on top of each other.
I need to do one of two things.
1. The simplest, copy the cells from each small array into a third array consisting of 42 columns with the number of rows equal to the number of smaller arrays. In the example above, I would end up with an array of 42 columns by 1,000 rows.
or more generally -
2. Extract only the information in certain, specific cells in each of smaller arrays and and place them in a third array with the number of columns equalling the number of cells copied.
I have tried to create a macro to do this but have been banging my head against the proverbial wall. I also tried to do it with QuicKeys but to no avail.
I'll gladly send someone a $50 gift certificate from Amazon.com if I use your solution. I'll also acknowledge you here.
1. I have a huge array 3 columns by well over 14,000 rows.
2. The array actually is a collection of a large number of smaller arrays of 3 columns by 14 rows.
3. Each cell in the smaller array represents a specific type of information - e.g. the cell in the top left is a date, the cell to its immediate right is a time. it looks like this
A B C
1 1 2 3
2 4 5 6
. . . .
. . . .
. . . .
13 37 38 39
14 40 41 42
15 1 2 3
and so on to well over 15,000 rows
If my large array actually had exactly 14,000 rows, there would be exactly 1,000 of the smaller arrays stacked on top of each other.
I need to do one of two things.
1. The simplest, copy the cells from each small array into a third array consisting of 42 columns with the number of rows equal to the number of smaller arrays. In the example above, I would end up with an array of 42 columns by 1,000 rows.
or more generally -
2. Extract only the information in certain, specific cells in each of smaller arrays and and place them in a third array with the number of columns equalling the number of cells copied.
I have tried to create a macro to do this but have been banging my head against the proverbial wall. I also tried to do it with QuicKeys but to no avail.
Comments
So every 14 rows, take each of the 3 (14 elem columns), concatenate them and then transpose into a single 42 elem row? For a 3x14K array you would end up with a 42x1K array. Is this what u want?
I think this is best handled by AppleScript, because you can loop through and even code it semi-automatically. I have the manual somewhere - it's different between Excel X and Excel 2004.
Dave
To Lundy - I am using Excel 2004 - Also Leopard on a MacPro
To Dave K - Actually, I thought the example I posted was "real" data but somehow the spacing got messed up when the thread was posted. The A B C along the top row was supposed to have been Excel column labels while the values in the first column was supposed to have been row numbers.
I'll try again
Column Numbers
Row A B C D
1 Nov 11 11:34 PM 434
2 28 42 273
3 123 256 144
4 ... ... ...
5 ... ... ...
6 ... ... ...
13 25 435 563
14 233 8 15
15 Nov 12 2:25 AM 126
And so on. Think of the data as a number of 3 x 14 matrices stacked one on top of the other.
I want to convert each matrix into a 42 x 1 matrix where cell A1 maps into the first cell of the target matrix and so on.
FYI - I imported the data from a company webpage as a delimited data set. I tried all sorts of delimiters in Excel (tab, comma, etc but the 3 X 15000 rows was the best Excel could do.
Thanks again, everybody -
John
You will need two nested loops. The outer one will select each of the 14 rows by 3 column ranges, one at a time, by adding 14 to a variable that you use as the row address of the range to work on. The inner one will process each of the 14 rows within that range and for each row, do three things:
A) copy three cells of the current row of the source
C) Increment that variable by 3.
I don't know VB, but here is some pseudocode:
set startofsource to R1C1
set startoftarget to R1C16 (arbitrary target)
for block=row of (startOfSource) to 15000 by 14 --do 1000 blocks
if cell 1 of block is blank then return --done
for rowOfBlock = block to block+14
copy cells in columns 1 to 3 of rowOfBlock
paste at startOfTarget
set column of (startOfTarget) to column of (startOfTarget) + 3
next rowOfBlock
set row of (startOfTarget) to row of (startOfTarget) + 1
next block
end sub
Or if its a one time thing post the source data somehow and I'll run it myself and send it to u.
A1 A2 ... A14 B1 B2 ... B14 C1 C2 ... C14
OR
A1 B1 C1 A2 B2 C2 ... A14 B14 C14
Function ArrayReshape(InputArray, NumberRows, NumberColumns, _\t\t\t\t\t\t
Optional ByColumns As Integer = -1)\t\t\t\t\t\t
'This function returns a rectangular, NumberRows X NumberColumns,\t\t\t\t\t\t
'array containing the elements of the input array or range.\t\t\t\t\t\t
'Without the optional argument it fills the output array\t\t\t\t\t\t
'row by row; with the optional argument (i.e., 1), it fills\t\t\t\t\t\t
'the output array column by column
The link is http://home.pacbell.net/beban/
Click the link at the bottom of the page to download the .xls file that has the macros.