Transpose Horizontal to Vertical with multiple key columns

**deuteron** · 04-15-2015, 07:51 PM

Hello,

I track operational metrics in Excel 2007, and for ease of forecasting, this is laid out with descriptive columns then time frame columns. 72 columns across in total. ~5,300 rows. Sample below - file attached.

Each data row has 6 columns that identify the:
A Metric (ie Received Units)
B Ledger (Actual/Forecast)
C Acct # (Metric Acct #)
D Dept# (Physical Location of activity)
E Partner # (client ID #)
F Line Description (client Name)

From here the columns are either months or weeks of the year (we track by fiscal week Sat through Sun) on a 4-4-5 calendar basis.

G Jan
H Feb
...
R Dec
S Total
U Week1
V Week2
...
BT Week 52

Functionally, this works great for forecasting, but stinks for reporting.

I can't figure out how to generate a vertical list with the first 6 columns (A through F above), column G would be the date/week number (G through BT above) and column H would be the data point.

FROM:
Account Description Ledger Account # Dept # Partner # Line Description Jan Feb Mar Apr ..........
Received Units ACTUALS 99002001 009999 PT.000001 CL001 140,802 52,807 53,756 29,335 ..........

TO:
Account Description Ledger Account # Dept # Partner # Line Description Timeframe Data
Received Units ACTUALS 99002001 009999 PT.000001 CL001 Jan 140,802
Received Units ACTUALS 99002001 009999 PT.000001 CL001 Feb 52,807
Received Units ACTUALS 99002001 009999 PT.000001 CL001 Mar 53,756
Received Units ACTUALS 99002001 009999 PT.000001 CL001 Apr 29,335
.........

Sample File attached

This data will need to be 'flipped'/'converted' frequently as forecasts and actual data changes.

Help!

Thanks for your time.

Sample Metric Data.xlsx

**LokeshKumar** · 04-16-2015, 02:55 AM

Hi,

Your explanation is awesome.....surely I will give you 1 click solution.....

Cheers......

**LokeshKumar** · 04-16-2015, 04:12 AM

Hi,

Here is your code... you can run it by pressing Ctr + Shift + K

Option Explicit
Sub TransposeMetricData()
    
    Dim I As Integer, j As Integer, k As Integer
    Dim Headrng As Range
    Dim Monthrng As Range
    Dim TotalRecords As Integer
    Dim LastRow As Integer
    Dim Filldown As Integer
    Dim Sh1 As Worksheet, Sh2 As Worksheet
        
        With Application
            .ScreenUpdating = False
            .CutCopyMode = False
        End With
        
        Set Sh1 = ThisWorkbook.Sheets(1)
        Set Sh2 = ThisWorkbook.Sheets(2)
            
            Set Headrng = Sh1.Range("A1:F1")
            Set Monthrng = Sh1.Range("G1:BT1")
            
            TotalRecords = Sh1.Range("A80000").End(xlUp).Row - 1
            
            Sh2.Activate
                Headrng.Copy Range("A1")
                Range("G1:H1") = Array("Timeframe", "Data")
                
                
                For I = 1 To TotalRecords
                    
                    LastRow = Range("A80000").End(xlUp).Row + 1
                    Headrng.Offset(I, 0).Copy Sh2.Range("a" & LastRow)
                    Monthrng.Copy
                    Sh2.Range("G" & LastRow).PasteSpecial Transpose:=True
                    Monthrng.Offset(1, 0).Copy
                    Sh2.Range("H" & LastRow).PasteSpecial Transpose:=True
                    Filldown = Range("H80000").End(xlUp).Row
                    Range("A" & LastRow, Range("F" & Filldown)).Select
                    Selection.Filldown
                    
                
                Next I
                
                Columns("A:H").AutoFit
                Msgbox "Done!"
                
            With Application
            .ScreenUpdating = True
            .CutCopyMode = True
            End With
                
End Sub

Here is your attachment...Sample.xlsm

Cheers.....
If your question is resolved, mark it SOLVED using the thread tools. Click on the star if you think some-1 helped you.

**deuteron** · 04-27-2015, 05:26 PM

This is 99% perfect - thank you!

What happens is that there will be a blank row(s) in the source data.
Row 183, the data will be there in columns A:D of that row, but either nothing or zeros in E:F, and nothing or sometimes zeros through column BT. I don't control the data to force to zero.
When I run the code, it pastes the values of the 1st row of the source data into this null space. Row 2 data showed up again in row 183. I have no idea how.
I've run this a few times, and can't come up with how it's getting the numbers to paste.
Any ideas?

Now I have three extra wrinkles.
1) Is there a way to skip blank rows? There are gaps between sections of data. Below row 183, there are 5 rows of either nothing or junk until row 188. The original data had headers for each section, that is the 'junk'. Again, i can't control the data coming to me, other than to manually alter it each time I get it - and that is frequent.

2) Since my initial request above, I found out that the output needs to be in three columns depending on what the data represents. In other words: Data needs to be broken up into 'Units', 'Orders', 'Packages'. For instance, row 2 through 183 are Units. Row 188 starts Packages, and so on. The first row of each section has the same column labels except column B where it is either Units, Orders, or Packages.
Is there a way to output that?

3) Is there a way to NOT show output rows with zeros as data points in all three data columns?

This data ultimately becomes part of a larger Access Db, and the less table space, the better.

Thanks again for the work so far.

Transpose Horizontal to Vertical with multiple key columns

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Transpose Horizontal to Vertical with multiple key columns

Re: Transpose Horizontal to Vertical with multiple key columns

Re: Transpose Horizontal to Vertical with multiple key columns

Re: Transpose Horizontal to Vertical with multiple key columns

Thread Information

Users Browsing this Thread

Similar Threads

Transpose calculated value from horizontal to vertical?

[SOLVED] Converting Vertical Columns in to Horizontal Rows - (Better solution to Transpose)

[SOLVED] Transpose Horizontal to Vertical with ID Column

[SOLVED] Excel 2007 : Can't transpose multiple horizontal rows of data to a vertical list

Arrange vertical string into multiple horizontal columns

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions