VBA Macro to transpose data and insert new data

[Fiveshorter]

Hi All,

I get a weekly report from our systems in form as a excel sheet. However the data is not structured in the way we would like it presented. Every week I open up the report (usually contains at least 500 rows), on every row I transpose the data and insert new suppliers for every price ( I will show this below )

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[/TR]
[TR]
[TD]HSK[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]YUI[/TD]
[TD]00[/TD]
[TD]21[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]28[/TD]
[TD]90[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

This would be the layout of my report that I get, sometimes I will have less then 3 prices for a particular item (could only have P1 and P2. When I receive this report I then convert to this :
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HSK[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HSK[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HSK[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YUI[/TD]
[TD]00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YUI[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YUI[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

After this, I insert particular supplier for every price ( note this is the same for every record on the sheet at the time of the report being run ) i.e for P1 I would be told the supplier code is X so for every P1 price the supplier Is X for all items.
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]P[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HSK[/TD]
[TD]20[/TD]
[TD]Y3A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HSK[/TD]
[TD]40[/TD]
[TD]ERT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HSK[/TD]
[TD]50[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YUI[/TD]
[TD]00[/TD]
[TD]Y3A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YUI[/TD]
[TD]21[/TD]
[TD]ERT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YUI[/TD]
[TD]28[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]28[/TD]
[TD]Y3A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]90[/TD]
[TD]ERT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]20[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If anyone can help in order to build a macro that I can simply run, enter in how many pricings (I,e P1,P2, P3 = 3)
what each pricing belongs to what supplier (p1 = y3a, p2 = 21, p3 = 20) and transpose the data in the way presented It would be greatly appreciated. Just a quick note, I don't need header names, the bulk of my time does be taking up by transposing the data and copy and pasting the same information for every item.

If there is any questions feel free to ask, thanks!

 

[alansidman]

This will transpose your data

Sub Normalize()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lrt As Long
    Dim lc As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False


    For i = 2 To lr
        lrt = s2.Range("B" & Rows.Count).End(xlUp).Row + 1
        lc = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        s1.Range("A" & i).Copy s2.Range("A" & lrt)
        s1.Range(Cells(i, 2), Cells(i, lc)).Copy
        s2.Range("B" & lrt).PasteSpecial xlPasteValues, , , True
        Application.CutCopyMode = False
    Next i


    lrt = s2.Range("B" & Rows.Count).End(xlUp).Row
    For i = 3 To lrt
        If s2.Range("A" & i) = "" Then
            s2.Range("A" & i) = s2.Range("A" & i - 1)
        End If
    Next i


    Application.ScreenUpdating = True


End Sub