Solved

Excel row and column as one record

Posted on 2014-07-21
1
593 Views
Last Modified: 2014-07-23
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
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 40208707
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question