Macro needed to transpose data

beevel · Apr 6th 2011

I need to transpose and duplicate a lot of data in order to put it into pivot table format. Attached is an example of a small excerpt of the raw data, and how the data should look after manipulation.

Description of data manipulation: Values in column A are duplicated for one full set of serial numbers (C1:U1), serial numbers are transposed into column B once for every different value in column A, Value1 and Value2 should be transposed into columns C&D respectively. I know this sounds a bit long winded, but the file should explain it quite clearly.

Currently I’m doing this manually and it takes an extremely long time, so was hoping someone could help me to put together a macro to automate it.

I should add that the amount of serial numbers, values in column A are not constant from report to report.

Any help would be much appreciated, thanks!

Wigi · Apr 6th 2011

Re: Macro needed to transpose data

For instance:

Code

Sub transposing_advanced()


    Dim l As Long, lNewRow As Long
    
    With Range("C1", Cells(3, Columns.Count).End(xlToLeft))
        For l = 2 To Range("A" & Rows.Count).End(xlUp).Row Step 2
            lNewRow = Range("AA" & Rows.Count).End(xlUp).Offset(1).Row
            Range("AA" & lNewRow).Resize(.Columns.Count) = Range("A" & l)
            Range("AA" & lNewRow).Offset(, 1).Resize(.Columns.Count) = WorksheetFunction.Transpose(.Value)
            Range("AA" & lNewRow).Offset(, 2).Resize(.Columns.Count, 2) = WorksheetFunction.Transpose(.Offset(l - 1).Resize(2).Value)
        Next
    End With


End Sub

Display More

Wigi

beevel · Apr 21st 2011

Re: Macro needed to transpose data

Thanks for the reply, and sorry for the very late response but i'm finding that this macro isn't doing anything to the data when i place it in the first sheet (raw data) of the uploaded file and run it.

I'm very new to macros, so this is probably due to an error on my part. should i be using a particular active cell? or placing it in the second sheet?

Many thanks

Wigi · Apr 21st 2011

Re: Macro needed to transpose data

Did you look at columns AA and the columns to the right? The output will be there...

Herbds7 · Apr 22nd 2011

Re: Macro needed to transpose data

Excel 2010 PivotTable
No need to transpose
No formulas, no code used.
Method can be applied to earlier Excel versions.
http://c3017412.r12.cf0.rackcdn.com/04_22_11.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
http://www.mediafire.com/file/ax8yalntrgcg3lr/04_22_11.pdf

AAE · Apr 22nd 2011

Re: Macro needed to transpose data

Herbds7,

Thanks for helping to provide solutions.

Getting the data from the original format to the table you show may not be clear to the OP and other users who view this thread/attachment.
Would be helpful if you provided explanations on the process/steps taken.

Thanks.

Macro needed to transpose data

Participate now!

Share