Transposing column into multiple rows

**jdubbie** · 02-16-2010, 12:13 PM

Hi all,

I've got an issue where I'm trying to transpose data from one column into several rows. I've been looking for a macro to help me out but can't seem to find a way to do this. Does anyone have any idea how I can do this?

Ideally, the macro would be written so it would find the data in the column and move it to a new sheet and then it would repeat the process throughout the document. The macro would know that the data is grouped b/c of the blanks found between the data set. So as the macro is running, once it hits a blank, it would then copy and transpose the data and continue. Does this make sense?

I've posted a sample of the info I'm working with.

**Richard Buttrey** · 02-16-2010, 12:31 PM

Hi,

Enter in C1 the following and copy across to P1

Please Login or Register  to view this content.

Now copy C1:P1 and paset it down to row 2417.

HTH

**jdubbie** · 02-16-2010, 12:44 PM

Hi Richard,

Thanks for the quick response. I really appreciate you helping out with this. I tried it but it appears that once it's copied down, it doesn't reflect the same data, as I think (guessing here) that the formula is set to read 15 rows and then transpose?

The data in the column differs as some may only have 13 rows and others have 15 or even 16. Does that make sense?

Thanks!
Jason

**Richard Buttrey** · 02-16-2010, 01:25 PM

Hi Jason,

Yes that would tend to screw it up

Before I give some thought as to how to automatically add rows where necessary so that each block consist of 16 rows (if that's the maximum), so that the formulae will work, is it possible to extract the data from your source system so that it automatically includes blank rows.

The other question is, apart form missing descriptors are all the records consistent in that the same descriptor always follows the previous descriptor in each block?

Regards

**jdubbie** · 02-16-2010, 01:28 PM

Hi all,

I've got an issue where I'm trying to transpose data from one column into several rows. I've been looking for a macro to help me out but can't seem to find a way to do this. Does anyone have any idea how I can do this?

Ideally, the macro would be written so it would find the data in the column and move it to a new sheet and then it would repeat the process throughout the document. The macro would know that the data is grouped b/c of the blanks found between the data set. So as the macro is running, once it hits a blank, it would then copy and transpose the data and continue. Does this make sense?

I've posted a sample of the info I'm working with and what i'm looking to do.

Many thanks!

**marc5354** · 02-16-2010, 01:45 PM

In C2 and copy down and across, =INDEX($A:$A, 5 * ROW() + COLUMN() - 12)

**marc5354** · 02-16-2010, 01:47 PM

Adjust this number: =INDEX($A:$A, 5 * ROW() + COLUMN() - 12)

**MickG** · 02-16-2010, 01:59 PM

Hi, If you have data in column "A" seperated by blank rows then this code will copy the Seperate sub sets to sheet (2) and transpose it to rows.

Please Login or Register  to view this content.

Regards Mick

**ptm0412** · 02-16-2010, 02:20 PM

There may be another way faster, but this code would work:

PHP Code:


Sub Trans()
Dim Rng As String
    RowCount = Sheets("info").[b100000].End(xlUp).Row + 1
    FirstR = 1: CountR = 0: TmpHeight = 0
    Do
        CountR = CountR + 1
        TmpHeight = TmpHeight + 1
        If Sheets("info").Cells(CountR, 2) = "" Then
            Rng = Range(Cells(FirstR, 2), Cells(CountR, 2)).Address
            Sheets("result").Cells(100000, 1).End(xlUp).Offset(1, 0).Resize(, TmpHeight) = _
            WorksheetFunction.Transpose(Sheets("info").Range(Rng))
            FirstR = CountR + 1
            TmpHeight = 0
        End If
    Loop Until CountR >= RowCount
Sheets("result").Select
EndR = Sheets("result").[a100000].End(xlUp).Row
Range("A1:A" & EndR).Select
Selection.AutoFilter field:=1, Criteria1:=0
Selection.Offset(1).EntireRow.Delete
Selection.AutoFilter

End Sub

The last 6 command lines are writen because I have seen that there are somewhere 2 blank rows.

**jdubbie** · 02-16-2010, 03:12 PM

That did it! thank you all for taking a look at this! i really appreciate it!!

**teylyn** · 02-16-2010, 04:34 PM

Jdubbie, you've started two threads about the same topic. This is duplicate posting and not allowed in this forum.

Since both of your threads have received several responses, I will not lock them, as I normally would.

Please take a moment to read the forum rules before you post again.

**romperstomper** · 02-17-2010, 06:58 AM

Doesn't appear to have anything to do with 2008 though...

**teylyn** · 02-17-2010, 07:21 AM

jdubbie, I've merged the two threads into one and left it in the Programming Forum, where it is more appropriately located. Please take care in the future to select the correct forum and post only one thread about one topic.

thanks

Transposing column into multiple rows

LinkBack

Thread Tools

Rate This Thread

Display

Transposing column into multiple rows

Re: Transposing column into multiple rows

Re: Transposing column into multiple rows

Re: Transposing column into multiple rows

Macro for transposing column into multiple rows?

Re: Macro for transposing column into multiple rows?

Re: Macro for transposing column into multiple rows?

Re: Macro for transposing column into multiple rows?

Re: Transposing column into multiple rows

Re: Macro for transposing column into multiple rows?

Re: Transposing column into multiple rows

Re: Transposing column into multiple rows

Re: Transposing column into multiple rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions