Excel row and column as one record

Hello Experts Exchange
I have a spreadsheet that has a table of data, the has product information going down the left of the row and date information going across the top.

Is it possible to get the product information and the date information in one row in Excel, so I can import the records into SQL Server?

I have attached how the data is on sheet1 and on sheet2 I have added a example of how I want the data.

Regards

SQLSearcher
Losses-Example.xls
SQLSearcherAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
This macro seems to do the trick for me.  Make sure to update the start date, and the range to evaluate (in that range, do not include the total column).

Sub Normalize()
    
    Dim SourceRange As Range
    Dim SourceArr As Variant
    Dim r As Long, c As Long
    Dim DestR As Long
    
    ' Update as needed
    
    Const StartDate As Date = #6/1/2014#
    
    ' Update as needed
    
    Set SourceRange = ActiveSheet.Range("a1:ak13")
    
    SourceArr = SourceRange.Value
    
    Worksheets.Add
    
    Range("a1:f1").Value = Array("Die", "Machine", "Part Desc", "Part No", "Date", "Units")
    DestR = 2
    
    For r = 4 To UBound(SourceArr, 1)
        For c = 7 To UBound(SourceArr, 2)
            If SourceArr(r, c) <> "" Then
                Cells(DestR, 1) = SourceArr(r, 1)
                Cells(DestR, 2) = SourceArr(r, 2)
                Cells(DestR, 3) = SourceArr(r, 3)
                Cells(DestR, 4) = SourceArr(r, 4)
                Cells(DestR, 5) = StartDate + SourceArr(1, c) - 1
                Cells(DestR, 6) = SourceArr(r, c)
                DestR = DestR + 1
            End If
        Next
    Next
    
    MsgBox "Done"
    
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.