transpose column data to row data

**davem1901** · 12-19-2010, 03:22 PM

Not a complete novice but a long way from where I want to be. Apologies for forum faux pax in advance.
I have a large spreadsheet with all of the data in column A. The data is currently in a 1 column/5 row blocks. I need to write a macro that takes each 1 column/5 row block of data and transpose each 1 column/5 row block into a 5 column/1row block of data.
I have attached a short sample of what I need to do with column A the current data format and columns B-F the desired data format. The spreadsheet has some 5000 rows in the current format.
I tried searching in the forum for this question and it kept giving me the following error.:
Fatal error: Allowed memory size of 100663296 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155

Thanks for any and all suggestions.
DaveM

**mrice** · 12-19-2010, 04:44 PM

Try this macro

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**TMS** · 12-19-2010, 04:47 PM

Something like:

Please Login or Register  to view this content.

Regards

**davem1901** · 12-20-2010, 09:38 AM

Martin,
Tried sending this before logging in, so want to make sure you got it. Can't thank you enough. Worked perfectly (once I got the errors in the data corrected.) Now I just have to go through it and see how you worked your magic.
Right now, afraid all I can do is to wish you well in your marathon and that you are able to raise a ton of money through. I admire your efforts to do so.
Dave Miller

**davem1901** · 12-20-2010, 09:41 AM

TM,
Thanks for your reply. I have not tried it yet but do plan of trying it. The first reply did the trick. However, yours looks like it uses more traditional methods that may help me to understand how this is accomplished and this is why we are all here I presume. Thanks again.
DaveM