# EXCEL Macro - HELP! Please -

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

141memberSo 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?

4,466memberI 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.

1,306memberDave

4memberTo 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

4member4,466memberYou 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

paste the cells into the current position in the single-row target (the column number is kept in a variable)

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

141memberOr if its a one time thing post the source data somehow and I'll run it myself and send it to u.

141memberA1 A2 ... A14 B1 B2 ... B14 C1 C2 ... C14

OR

A1 B1 C1 A2 B2 C2 ... A14 B14 C14

1,888member1,888member4,466memberFunction 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.