CODEWITHSUNDEEP
excelvba
Shezi
Shezi

Reputation: 1342

MSEXCEL Transpose data into database format

I am uploading a Tabular data into my SQL database.

The following is the original look of the data.

enter image description here

I want to take each temperature (Y axis) in a column and then each gravity (X Axis) in next Column, followed by the intersecting value.

Like This:

enter image description here

Upvotes: 2

Views: 991

Answers (1)

Shezi
Shezi

Reputation: 1342

i got helped from a guy from ExcelForum. Here is the Macro Code, that solved my problem.

       Option Explicit

    Sub transpose_data()
    Dim lrow As Long, lcol As Long, a As Long, i As Long, j As Long

    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Output"
    a = 1
    With Worksheets("Original")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        lcol = .Range("IV1").End(xlToLeft).Column
        For i = 2 To lrow
            For j = 2 To lcol
                Worksheets("Output").Cells(a, 1).Value = .Cells(i, 1).Value
                Worksheets("Output").Cells(a, 2).Value = .Cells(1, j).Value
                Worksheets("Output").Cells(a, 3).Value = .Cells(i, j).Value
                a = a + 1
            Next j
        Next i
    End With

Worksheets("Output").Columns("A:C").NumberFormat = "0.00"

End Sub

Make sure to change the Sheet name to 'Original' in order to have this code working.

Upvotes: 2

Related Questions