Formula to make a record sheet out of a report..

[omairhe]

Hello all,

I'm trying to get a record stored for later use from a givenreport for the month of February, 2018. Because the report is Excel based Iwould like to store the data in such a way that Pivot table can be used for anyfuture requirements.

Currently the report looks like this...

However my goal is to achieve something like theimage below in a new sheet:-

Will appreciate a formula more than a Vbascript. But either way it will mean the world to me...
Tk you.

regards,
omair
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

 

[cerfani]

Sub FormatTable()
    Dim wb As Workbook
    Dim s As Worksheet, ss As Worksheet
    Dim r As Long, c As Long, pr As Long


    Set wb = ThisWorkbook 'Workbooks("Book1.xlsx")
    Set s = wb.Worksheets("Sheet1")
    Set ss = wb.Worksheets.Add(After:=s)
    pr = 1


    For c = 2 To s.Cells(1, Columns.Count).End(xlToLeft).Column
        For r = 2 To s.Cells(s.Rows.Count, 1).End(xlUp).Row
            ss.Cells(pr, 1).Value = s.Cells(r, 1).Value
            ss.Cells(pr, 2).Value = s.Cells(1, c).Value
            ss.Cells(pr, 3).Value = s.Cells(r, c).Value


            pr = pr + 1
        Next r
    Next c
End Sub

this would do it

 

[omairhe]

Thank you... I appreciate it a lot.

Sub FormatTable()
    Dim wb As Workbook
    Dim s As Worksheet, ss As Worksheet
    Dim r As Long, c As Long, pr As Long


    Set wb = ThisWorkbook 'Workbooks("Book1.xlsx")
    Set s = wb.Worksheets("Sheet1")
    Set ss = wb.Worksheets.Add(After:=s)
    pr = 1


    For c = 2 To s.Cells(1, Columns.Count).End(xlToLeft).Column
        For r = 2 To s.Cells(s.Rows.Count, 1).End(xlUp).Row
            ss.Cells(pr, 1).Value = s.Cells(r, 1).Value
            ss.Cells(pr, 2).Value = s.Cells(1, c).Value
            ss.Cells(pr, 3).Value = s.Cells(r, c).Value


            pr = pr + 1
        Next r
    Next c
End Sub

this would do it

 

[cerfani]

No prob!