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:

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

<colgroup><col span="2"><col span="2"><col span="11"></colgroup><tbody>
</tbody>

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

Item # whse #	Date	Sales
19617.36	Month 1	2,849.00
19617.36	Month 2	33,784.00
19617.36	Month 3	18,491.00
19617.36	Month 4	31,193.00
19617.36	Month 5	38,032.00
19617.36	Month 6	14,148.00
19617.36	Month 7	28,749.00
19617.36	Month 8	34,613.00
19617.36	Month 9	27,816.00
19617.36	Month 10	16,221.00
19617.36	Month 11	24,953.00
19617.36	Month 12	32,125.00
19617.53	Month 1	35,520.00
19617.53	Month 2	18,645.00
19617.53	Month 3	26,702.00
19617.53	Month 4	8,937.00
19617.53	Month 5	7,144.00
19617.53	Month 6	-
19617.53	Month 7	-
19617.53	Month 8	-
19617.53	Month 9	30,295.00
19617.53	Month 10	36,595.00
19617.53	Month 11	39,950.00
19617.53	Month 12	15,190.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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