Transpose variable row data into columns

**rjackson444** · 07-18-2011, 10:34 AM

I am new to the forum but have found some helpful stuff here so far.

I have a project that I am working on and have over 68 thousand records that have all the data in one column that I need to get into multiple columns. The good thing is each record is broken up by a blank row but some records could have 1 line or 6 lines that I need to move to columns. If there is only 1 row then it goes to column 6, 2 rows then they go to column 5 and 6, 3 lines column 4, 5 and 6...etc.

Example:
data
data
data
<blank row>
data
<blank row>
data
data
<blank row>
data
data
data

Desired result:
Column 1 Column 2 Column 3
this didnt come out right. if i need to i can poste the spreadsheet

Thanks,
Russell

**StephenR** · 07-18-2011, 11:29 AM

Russell - please post a workbook illustrating data and desired results.

EDIT: if I have understood correctly, perhaps this. It assumes data in column A of sheet 1 and puts results on sheet 2

Please Login or Register  to view this content.

**rjackson444** · 07-18-2011, 12:53 PM

Unfortunately I am not able to upload information from my pc at work. See if this is a better description.
current
data1
data2
data3

data21
data22

data31

data41
data42
data43
data44
data45

data51
data52
data53

data61
data62
data63

wanted results
Column1 Column2 Column3 Column4 Column5 Column6 Column7
data1 data2 data3
data21 data22
data31
data41 data42 data43 data44 data45

This is still not formatted correctly but the idea is to have the data go into its respective column.

So, yes there is data in column A and putting it on sheet 2 is fine if that is easier. So I want it transposed until a space is found, then get the next row and transpose until there is no more data. Range is to 68000.

Thanks

**StephenR** · 07-18-2011, 01:57 PM

So, have you tried the code?

**JBeaucaire** · 07-18-2011, 02:00 PM

The problem with SPECIAL CELLS is that is can only work with less than 8000 distinct "areas" at once. Your description of the issue makes me think the areas method will fail in a too-simple application across the whole dataset at once.

This version of the technique breaks your data down into groups of 10000 rows or so, to be safe, and processes them in groups. The result should be the same, but this is more defensive:

Please Login or Register  to view this content.

**JBeaucaire** · 07-18-2011, 02:41 PM

Originally Posted by StephenR

So, have you tried the code?

Heh, have you? I did, it failed just as expected on a dataset that large. Special Cells can't handle that many areas, so it just flops back to one big area and fails. Special Cells is one of my faves, but it's a tempermental critter. I'm glad the OP told us his data was that large..

**rjackson444** · 07-18-2011, 02:44 PM

Originally Posted by StephenR

So, have you tried the code?

I did but was getting Application-defined or object-defined error on line

Sheet2.Cells(n, 7 - r.Count).PasteSpecial Transpose:=True

Thanks,
Russell

**JBeaucaire** · 07-18-2011, 02:56 PM

Russell, use the more defensive approach from post #5.

**rjackson444** · 07-18-2011, 03:05 PM

It worked great JB, thanks much for the quick help.

Transpose variable row data into columns

LinkBack

Thread Tools

Rate This Thread

Display

Transpose variable row data into columns

Re: Transpose variable row data into columns

Re: Transpose variable row data into columns

Re: Transpose variable row data into columns

Re: Transpose variable row data into columns

Re: Transpose variable row data into columns

Re: Transpose variable row data into columns

Re: Transpose variable row data into columns

Re: Transpose variable row data into columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions