Data manipulation question

[tforthofer]

Hello,

I have some data for some sales information that I am trying to organize in a different manner. I have around 5000+ row of data formatted like so:

[TABLE="width: 1018"]
<colgroup><col span="2"><col span="2"><col span="11"></colgroup><tbody>[TR]
[TD]Item #[/TD]
[TD]Whse[/TD]
[TD]Item # whse #[/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD]Month 4[/TD]
[TD]Month 5[/TD]
[TD]Month 6[/TD]
[TD]Month 7[/TD]
[TD]Month 8[/TD]
[TD]Month 9[/TD]
[TD]Month 10[/TD]
[TD]Month 11[/TD]
[TD]Month 12[/TD]
[/TR]
[TR]
[TD]19617[/TD]
[TD]36[/TD]
[TD]19617.36[/TD]
[TD]2849[/TD]
[TD]33784[/TD]
[TD]18491[/TD]
[TD]31193[/TD]
[TD]38032[/TD]
[TD]14148[/TD]
[TD]28749[/TD]
[TD]34613[/TD]
[TD]27816[/TD]
[TD]16221[/TD]
[TD]24953[/TD]
[TD]32125[/TD]
[/TR]
[TR]
[TD]19617[/TD]
[TD]53[/TD]
[TD]19617.53[/TD]
[TD]35520[/TD]
[TD]18645[/TD]
[TD]26702[/TD]
[TD]8937[/TD]
[TD]7144[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30295[/TD]
[TD]36595[/TD]
[TD]39950[/TD]
[TD]15190[/TD]
[/TR]
[TR]
[TD]10115[/TD]
[TD]36[/TD]
[TD]10115.36[/TD]
[TD]11327[/TD]
[TD]24648[/TD]
[TD]10029[/TD]
[TD]13258[/TD]
[TD]18494[/TD]
[TD]9417[/TD]
[TD]36525[/TD]
[TD]13366[/TD]
[TD]21913[/TD]
[TD]3479[/TD]
[TD]13923[/TD]
[TD]10101[/TD]
[/TR]
[TR]
[TD]11344[/TD]
[TD]36[/TD]
[TD]11344.36[/TD]
[TD]38686[/TD]
[TD]31932[/TD]
[TD]4111[/TD]
[TD]26127[/TD]
[TD]35904[/TD]
[TD]31832[/TD]
[TD]39997[/TD]
[TD]28886[/TD]
[TD]24905[/TD]
[TD]36690[/TD]
[TD]37451[/TD]
[TD]38522[/TD]
[/TR]
[TR]
[TD]19770[/TD]
[TD]53[/TD]
[TD]19770.53[/TD]
[TD]10085[/TD]
[TD]17891[/TD]
[TD]14480[/TD]
[TD]29382[/TD]
[TD]4709[/TD]
[TD]16587[/TD]
[TD]263[/TD]
[TD]34041[/TD]
[TD]14846[/TD]
[TD]12596[/TD]
[TD]33473[/TD]
[TD]1373[/TD]
[/TR]
</tbody>[/TABLE]

I would like to have it look like this which me having to change the 5000+ lines every month.

[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item # whse #
[/TD]
[TD]Date
[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 1
[/TD]
[TD] 2,849.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 2[/TD]
[TD] 33,784.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 3[/TD]
[TD] 18,491.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 4[/TD]
[TD] 31,193.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 5[/TD]
[TD] 38,032.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 6[/TD]
[TD] 14,148.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 7[/TD]
[TD] 28,749.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 8[/TD]
[TD] 34,613.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 9[/TD]
[TD] 27,816.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 10[/TD]
[TD] 16,221.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 11[/TD]
[TD] 24,953.00[/TD]
[/TR]
[TR]
[TD]19617.36[/TD]
[TD]Month 12[/TD]
[TD] 32,125.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 1[/TD]
[TD] 35,520.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 2[/TD]
[TD] 18,645.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 3[/TD]
[TD] 26,702.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 4[/TD]
[TD] 8,937.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 5[/TD]
[TD] 7,144.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 6[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 7[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 8[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]19617.53
[/TD]
[TD]Month 9[/TD]
[TD] 30,295.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 10[/TD]
[TD] 36,595.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 11[/TD]
[TD] 39,950.00[/TD]
[/TR]
[TR]
[TD]19617.53[/TD]
[TD]Month 12[/TD]
[TD] 15,190.00
[/TD]
[/TR]
</tbody>[/TABLE]


I'm stuggling to find a way that I can do this and is easy repeatable every month. Any help would be greatly appreciated.

Thanks,

 

[Joe4]

Welcome to the Board!

A macro can be created to do this.
Can you just confirm what row your data starts on, and what columns the data is found in?

 

[NeonRedSharpie]

Sub transposeThings()
    'I should remember to save this macro in my macro sheet
    Dim raw As Worksheet
    Dim formatted As Worksheet
    Dim nextRow As Long
    
    nextRow = 2
    
    Set raw = Sheets("RAW") 'CHANGE ME
    Set formatted = Sheets("FORMATTED") 'CHANGE ME
    
    With formatted
        .Cells(1, 1) = "Item #/Whse #"
        .Cells(1, 2) = "Date"
        .Cells(1, 3) = "Sales"
    End With
    
    
    With raw
        For x = 2 To .Cells(Rows.Count, 1).End(xlUp).Row Step 1
            For y = 4 To 15
                formatted.Cells(nextRow, 1) = .Cells(x, 3)
                formatted.Cells(nextRow, 2) = .Cells(1, y)
                formatted.Cells(nextRow, 3) = .Cells(x, y)
                nextRow = nextRow + 1
            Next y
        Next x
    End With


End Sub