Flatten Data Script

Hi All

I have written a small bit of script which loops through data. It assumes that the data is sorted by employee. If there is more than one record for the same employee it will place certain fields onto the next available column for the top row of data for that employee (effectively flattening out history records on to one row)

The code does what I want but is intended to be run on a data set of > 300,000 records. When I have tried this it takes ages (I stopped it after 15 minutes and it had only done 14000 records).

I have tried to re-write this using "for, each, next" instead of activating each cell but I couldn't work out how to include the "If" and "Do While"

I have attached a sheet with the script and some sample records. I would really appreciate it if anyone could give me any pointers to speed this code up.

Thanks in advance

Regards

Jim

Hi,

Activating slows down the code. Also if you delete rows then you need to start at the bottom and work your way up.

Here is a modified code (I think this is what you are after):

Please Login or Register  to view this content.

Hope this helps.

abousetta

Instead of reading/writing/adapting the sheet this code puts all results into an array.
Only when the code is finished the results will be written into the sheet.
The result will be in the first empty column after the currentregion.
Texttocolumn will distribute the result into separate columns.

Please Login or Register  to view this content.

snb, I like your approach and I am wondering how much memory this will absorb since everything is being saved in the RAM until the code is completed. I will try to test this will variable rows and see how the performance does on larger and larger sets.

Thanks for sharing this approach.

abousetta

The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461.

Pike, that is really interesting. I didn't know about this limitation to transpose in vba. I am wondering what the workaround to this limition would be for large sets like the one that the OP mentioned (>300,000 rows)?

abousetta

possibly use "For Each in"

@abousetta

In my suggestion I use transpose only in a horizontal manner. So the limitation of transpose needs no 'workaround' since I assume the OP hasn't more than 5160 columns to deal with.