Excel 2010 :: Transposing Data With Blank Rows In Columns

We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)

Right now here's how the data is received:

a
a
a
b
b
c
c
c
c
d
d
d
e
e
e
e

Using two vba scripts, we first separate the data with row spaces between the unique data as follows:

a
a
a

b
b

c
c
c
c

Then with another script, we transpose the data as follows:

a a a
b b
c c c c
d d d
e e e e

When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...

a a a
b b
c c c c
d d d
e e e e e

where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.

The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.

Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?

My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.

I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)

The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.

One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.

We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.

For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.

One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.