Solved

Excel Table data to line row data

Posted on 2014-09-03
20
198 Views
Last Modified: 2014-09-10
Hello Experts Exchange
I have a spreadsheet that has a table of data, the has User information going down the left of the row and date information going across.

Is it possible to get the user 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.

If there is someone that can help me to write the macro to get the information the way I want it that would be great.

Regards

SQLSearcher
Excel-Time-and-Attendance.xls
0
Comment
Question by:SQLSearcher
  • 10
  • 10
20 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40302883
Do you need the daily values or just the total values?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40302934
Put this in a module and test it.
Option Explicit


Public Sub Q_28510582()
    Dim rngFind As Range
    Dim rngTgt As Range
    Dim rngSrc As Range
    Dim strFirstFind As String
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    
    Set wksSrc = Worksheets("Sheet1")
    Set wksTgt = Worksheets("Sheet3")
    Set rngTgt = wksTgt.Range("A2")
    
    Set rngFind = wksSrc.Range("A:A").Find("Clock Number:")
    strFirstFind = rngFind.Address
    Application.ScreenUpdating = False
    Do
        If Len(rngFind.Offset(-1).Value) <> 0 Then
            Set rngSrc = rngFind.Offset(-1, 0)
            wksTgt.Range(rngTgt, rngTgt.Offset(0, 1)).Value = wksSrc.Range(rngSrc, rngSrc.Offset(, 1)).Value
            wksTgt.Range(rngTgt.Offset(0, 2), rngTgt.Offset(0, 4)).Value = WorksheetFunction.Transpose(wksSrc.Range(rngSrc.Offset(0, 34), rngSrc.Offset(2, 34)))
            Set rngTgt = rngTgt.Offset(1)
        End If
        Set rngFind = wksSrc.Range("A:A").FindNext(rngFind)
    Loop Until rngFind.Address = strFirstFind
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:SQLSearcher
ID: 40302950
Hello aikimark
I need the daily values, your code give me the totals,  can you help me get the daily values?

Regards

SQLSearcher
0
 

Author Comment

by:SQLSearcher
ID: 40303077
Hello aikimark
How can I add the date and the clock number to the output?

Regards

SQLSearcher
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40303140
This version gloms the individual days and also transfers the date from B2
Option Explicit



Public Sub Q_28510582()
    Dim rngFind As Range
    Dim rngTgt As Range
    Dim rngSrc As Range
    Dim strFirstFind As String
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim strDate As String
    Dim lngDay As Long
    
    Set wksSrc = Worksheets("Sheet1")
    Set wksTgt = Worksheets("Sheet3")
    Set rngTgt = wksTgt.Range("A2")
    
    strDate = wksSrc.Range("B2").Value
    
    Set rngFind = wksSrc.Range("A:A").Find("Clock Number:")
    strFirstFind = rngFind.Address
    
    Application.ScreenUpdating = False
    
    Do
        If Len(rngFind.Offset(-1).Value) <> 0 Then
            rngTgt.Value = strDate
            Set rngTgt = rngTgt.Offset(0, 1)
            
            Set rngSrc = rngFind.Offset(-1, 0)
            wksTgt.Range(rngTgt, rngTgt.Offset(0, 1)).Value = wksSrc.Range(rngSrc, rngSrc.Offset(, 1)).Value
            Set rngTgt = rngTgt.Offset(0, 2)
            Set rngSrc = rngSrc.Offset(0, 3)
            For lngDay = 0 To 30
                wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = WorksheetFunction.Transpose(wksSrc.Range(rngSrc.Offset(0, lngDay), rngSrc.Offset(2, lngDay)))
                Set rngTgt = rngTgt.Offset(0, 3)
            Next
            Set rngTgt = wksTgt.Cells(rngTgt.Row + 1, 1)
        End If
        Set rngFind = wksSrc.Range("A:A").FindNext(rngFind)
    Loop Until rngFind.Address = strFirstFind
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:SQLSearcher
ID: 40303408
Hello aikimark
The new example is not quite what I want, I need the data to have a new line for each day for each user, the sample you have given me puts all the data in one line for each user and under one date.

Regards

SQLSearcher
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40303538
get the user information and the date information in one row in Excel
I based my code on that part of your question.

Before I make any more changes...
* Post an example of what your sample data should look like after transformation
* Since there are two apparent dates on the worksheet, what is the relationship between the date in B2 and the day-of-month slots?
0
 

Author Comment

by:SQLSearcher
ID: 40303727
Hello aikimark
Thanks to your input I have been able to write a macro to do what I need.

Please see the attached spreadsheet.

Thank you very much for your help.

Regards

SQLSearcher
Excel-Time-and-Attendance2.xls
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40303752
I think you have problems with your code, but if you're happy, that is what matters.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40304201
Some questions to take forward:
1. If the date in B2 is the 14th, does it make sense to grab any cell data after the 14th?
2. Where does the 7.5 hours come from?  I see it in your code, but don't know where it might have been referenced in your question or in the sheet1 contents.
3. What is going to happen next month?  You have hard-coded date values for Sept days.
4. There is no Sept 31 date
5. A "S" value in the attendance row is causing your column G formula to fail in a few rows
7. Why 5000 rows of output and not 527 (or fewer) rows?

Note: without an Option Explicit statement, you have undeclared variables that might cause you problems.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:SQLSearcher
ID: 40305419
Hello aikimark
Thank you for your questions.

1. If the date in B2 is the 14th, does it make sense to grab any cell data after the 14th?
I need to get all the data for each date.

2. Where does the 7.5 hours come from?  I see it in your code, but don't know where it might have been referenced in your question or in the sheet1 contents.
In = 7.5 this was not on my question and is not on the sheet.

3. What is going to happen next month?  You have hard-coded date values for Sept days.
I will have to replace all the dates and save the code.  Can you help me to make the code dynamic so I don't have to change the dates every month?

4. There is no Sept 31 date
When I import the file into SQL Server it ignore this date and sets it to a NULL, so I just delete Null dates, so I am ok with this.

5. A "S" value in the attendance row is causing your column G formula to fail in a few rows
I am not sure what I want to do with this yet.

7. Why 5000 rows of output and not 527 (or fewer) rows?
Which row the data will end is different each time I run the code, so I hard coded 5000. Is there a way in the macro I can set the amount of row dynamically?

Regards

SQLSearcher
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40305619
Please try this version of the routine.  There is a Select Case statement that contains the replace statements for the eight possible character-to-value transformations in the Attendance cells.  I went ahead and transformed "S"=>0.  You will need to update this section as you decide what should be done.
Public Sub Q_28510582()
    Dim rngFind As Range
    Dim rngTgt As Range
    Dim rngSrc As Range
    Dim strFirstFind As String
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim strSection As String
    Dim dtRptDate As Date
    Dim lngLastDayOfMonth As Long
    Dim lngDay As Long
    Dim vFirstLastNames As Variant
    Dim strAttendance As String
    
    Set wksSrc = Worksheets("Sheet1")
    Set wksTgt = Worksheets("Sheet3")
    Set rngTgt = wksTgt.Range("A2")
    
    dtRptDate = wksSrc.Range("B2").Value
    lngLastDayOfMonth = Day(DateAdd("m", 1, dtRptDate) - 1)
    strSection = wksSrc.Range("B3").Value
    
    Set rngFind = wksSrc.Range("A:A").Find("Clock Number:")
    strFirstFind = rngFind.Address
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Do
        If Len(rngFind.Offset(-1).Value) <> 0 Then
            Set rngSrc = rngFind.Offset(-1, 0)
            vFirstLastNames = wksSrc.Range(rngSrc, rngSrc.Offset(, 1)).Value
            Set rngSrc = rngSrc.Offset(0, 3)
            For lngDay = 1 To lngLastDayOfMonth
                wksTgt.Range(rngTgt, rngTgt.Offset(0, 1)).Value = vFirstLastNames
                Set rngTgt = rngTgt.Offset(0, 2)
                wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = WorksheetFunction.Transpose(wksSrc.Range(rngSrc.Offset(0, lngDay - 1), rngSrc.Offset(2, lngDay - 1)))
                strAttendance = rngTgt.Offset(0, 2).Value
                If Len(strAttendance) <> 0 Then
                    Select Case strAttendance
                        Case "in"
                            strAttendance = Replace(strAttendance, "in", "7.5")
                        Case "S"
                            strAttendance = Replace(strAttendance, "S", "0")
                        Case "K"    'the following replacements are undefined
                        Case "Q"
                        Case "H"
                        Case "C"
                        Case "NE"
                        Case "A"
                    End Select
                    rngTgt.Offset(0, 2).Value = strAttendance
                End If
                rngTgt.Offset(0, 3).Value = dtRptDate + lngDay - 1
                rngTgt.Offset(0, 4).FormulaR1C1 = "=RC[-2]-RC[-3]+RC[-4]"
                rngTgt.Offset(0, 5).Value = strSection
                
                Set rngTgt = wksTgt.Cells(rngTgt.Row + 1, 1)
            Next
        End If
        Set rngFind = wksSrc.Range("A:A").FindNext(rngFind)
    Loop Until rngFind.Address = strFirstFind
    
    Range("A1:H1").Value = Array("First Name", "Surname", "Overtime (hrs)", "Late / Ely off (hrs)", "Attendance", "Date", "TimeCal", "Cost Centre")
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:SQLSearcher
ID: 40305811
Hello aikimark
Thank you for the code you have given me, it run's much faster than mine.

Can I just ask you to make one more change please?  Is it possible to add the clock number to the data please?

Regards

SQLSearcher
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40305816
Where should the clock number go?
0
 

Author Comment

by:SQLSearcher
ID: 40307225
Hello aikimark
The clock numbers can go at the end after the cost centre.

Regards

SQLSearcher
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40307238
Clock version:
Public Sub Q_28510582()
    Dim rngFind As Range
    Dim rngTgt As Range
    Dim rngSrc As Range
    Dim strFirstFind As String
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim strSection As String
    Dim dtRptDate As Date           'mm/1/yyyy
    Dim lngLastDayOfMonth As Long
    Dim lngDay As Long
    Dim vFirstLastNames As Variant
    Dim strAttendance As String
    Dim strClockNumber As String
    
    Set wksSrc = Worksheets("Sheet1")
    Set wksTgt = Worksheets("Sheet3")
    Set rngTgt = wksTgt.Range("A2")
    
    dtRptDate = wksSrc.Range("B2").Value
    lngLastDayOfMonth = Day(DateAdd("m", 1, dtRptDate) - 1)
    strSection = wksSrc.Range("B3").Value
    
    Set rngFind = wksSrc.Range("A:A").Find("Clock Number:")
    strFirstFind = rngFind.Address
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Do
        If Len(rngFind.Offset(-1).Value) <> 0 Then
            strClockNumber = rngFind.Offset(0, 1)
            Set rngSrc = rngFind.Offset(-1, 0)
            vFirstLastNames = wksSrc.Range(rngSrc, rngSrc.Offset(, 1)).Value
            Set rngSrc = rngSrc.Offset(0, 3)
            For lngDay = 1 To lngLastDayOfMonth
                wksTgt.Range(rngTgt, rngTgt.Offset(0, 1)).Value = vFirstLastNames
                Set rngTgt = rngTgt.Offset(0, 2)
                wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = WorksheetFunction.Transpose(wksSrc.Range(rngSrc.Offset(0, lngDay - 1), rngSrc.Offset(2, lngDay - 1)))
                strAttendance = rngTgt.Offset(0, 2).Value
                If Len(strAttendance) <> 0 Then
                    Select Case strAttendance
                        Case "in"
                            strAttendance = Replace(strAttendance, "in", "7.5")
                        Case "S"
                            strAttendance = Replace(strAttendance, "S", "0")
                        Case "K"    'the following replacements are undefined
                        Case "Q"
                        Case "H"
                        Case "C"
                        Case "NE"
                        Case "A"
                    End Select
                    rngTgt.Offset(0, 2).Value = strAttendance
                End If
                rngTgt.Offset(0, 3).Value = dtRptDate + lngDay - 1
                rngTgt.Offset(0, 4).FormulaR1C1 = "=RC[-2]-RC[-3]+RC[-4]"
                rngTgt.Offset(0, 5).Value = strSection
                rngTgt.Offset(0, 6).Value = strClockNumber
                
                Set rngTgt = wksTgt.Cells(rngTgt.Row + 1, 1)
            Next
        End If
        Set rngFind = wksSrc.Range("A:A").FindNext(rngFind)
    Loop Until rngFind.Address = strFirstFind
    
    wksTgt.Range("A1:I1").Value = Array("First Name", "Surname", "Overtime (hrs)", "Late / Ely off (hrs)", "Attendance", "Date", "TimeCal", "Cost Centre", "Clock")
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:SQLSearcher
ID: 40310091
Hello aikimark
I have found a problem when trying to import the data into SQL Server and need another change please?

I'm having problems importing Overtime (hrs) & Late / Ely off (hrs).  If I set the blank fields to zero the SQL Server import works fine.

Can you change the code so that when Overtime (hrs) & Late / Ely off (hrs) is nothing it should be set to 0 please?

Regards

SQLSearcher
0
 

Author Comment

by:SQLSearcher
ID: 40310174
Hello aikimark
Please ignore my last comment, have change code myself.

Regards

SQLSearcher
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40310290
Also, ignore my comment about the 14th date in B2.  My mind didn't related 14 to the year (2014) abbreviation.
0
 

Author Closing Comment

by:SQLSearcher
ID: 40313939
Thank you aikimark, you have been a star, thank you so much for your help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now