EXCEL Macro - HELP! Please -

in Mac Software edited January 2014
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


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.


  • Reply 1 of 11
    Recording such a macro would be quite a lenghty process. I would suggest writing some code to manipulate this array. Do an export to comma or tab delimited text file and read from it, run through code and then import into excel.

    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?
  • Reply 2 of 11
    lundylundy Posts: 4,466member
    Which version of Excel? I ask because the AppleScript is different. I just looked through the Excel functions and even the TRANSPOSE one doesn't do what you 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.
  • Reply 3 of 11
    dave k.dave k. Posts: 1,306member
    Can you post some real data instead of ABC, 123 stuff. Even if you make it up so I can visualize what you are talking about...

  • Reply 4 of 11
    To KennyWRX - I may have to brrush up on Objective C (when I was in engineering school, they taught plain Ols "C".

    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 -

  • Reply 5 of 11
    Oops, the posting did it again, removing spaces from the example :-(
  • Reply 6 of 11
    lundylundy Posts: 4,466member
    OK, looking at the Visual Basic stuff, which I have seen for the first time today after 41 years of computer programming , I would suggest the following:

    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

    B) 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
  • Reply 7 of 11
    I would do it in LabVIEW cause thats what I use on a daily basis. You can download a Demo version from www.NI.com and I can send you the program that would do that. Just let me know.

    Or if its a one time thing post the source data somehow and I'll run it myself and send it to u.
  • Reply 8 of 11
    One thing you did not specify is if you want the output rows as:

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


    A1 B1 C1 A2 B2 C2 ... A14 B14 C14
  • Reply 9 of 11
    mydomydo Posts: 1,888member
    If I had your file I could do it in about 30mins but it would take me way longer to explain how to do it.
  • Reply 10 of 11
    mydomydo Posts: 1,888member
    Actually you could pull this off with a cell formula and a sort function.
  • Reply 11 of 11
    lundylundy Posts: 4,466member
    Here ya go - this guy has a package of array-manipulation macros that can do a lot. The one you want is the "Reshape Array" which can take a 2-D array and make it into a 1-D array, by rows.

    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.
Sign In or Register to comment.