Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: How to transpose only one column

  1. #1

    Question How to transpose only one column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi , I have a data sheet containing clock IN times in G1 and Clock OUT times in G2 according to the workers name department etc.. Now I would like to transpose the data so that one row will display clock in and clock out times. I want to transpose my data so that row 1 and 2 join and that clock IN and clock OUT times are shown next to each other rather than beneath each other For example
    EMP_NO
    NAME
    SURNAME
    COST_CENTRE
    DEPARTMENT
    GRADE
    CLKTIME
    IO
    Week
    Year
    Day
    Hours Worked
    000127 Jay Kirk 01 Prod 2 2011-10-10 07:00:00 AM I 42 2014 Mon x
    000127 Jay Kirk 01 Prod 2
    2011-10-10 17:00
    O 42 2014 Mon x
    etc

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    Will there always be both clock in and out, or do we need to cater for one (or either?) missing?

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Will there always be both clock in and out, or do we need to cater for one (or either?) missing?
    Sometimes a clocking is missing, In that case I will need it to show me whether the in or out is missing. Example: On my current data, sometimes a clocking gets missing so... Row 1 will show Sam clocking in on Jan 1 and directly beneath that Sam will clock in on Jan 2... where it should show clock in time Jan 1 and clock out time Jan 1 beneath each other (clock out is missing)

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    Code:
    Option Explicit
    
    Public Sub MergeData()
    Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            .Columns("I").Insert
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = lastrow To 2 Step -1
            
                If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
                
                    If Int(.Cells(i, "G").Value) = Int(.Cells(i - 1, "G").Value) Then
                
                        If .Cells(i, "H").Value = "O" Then
                        
                            .Cells(i - 1, "I").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                            .Cells(i - 1, "H").Value = .Cells(i - 1, "G").Value - Int(.Cells(i - 1, "G").Value)
                            .Cells(i - 1, "G").Value = Int(.Cells(i - 1, "G").Value)
                            .Rows(i).Delete
                            i = i - 1
                        End If
                    Else
                        If .Cells(i, "H").Value = "I" Then
                        
                            .Cells(i, "H").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                        Else
                        
                            .Cells(i, "H").ClearContents
                            .Cells(i, "I").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                        End If
                        .Cells(i, "G").Value = Int(.Cells(i, "G").Value)
                        
                        If .Cells(i - 1, "H").Value = "I" Then
                        
                            .Cells(i - 1, "H").Value = .Cells(i - 1, "G").Value - Int(.Cells(i - 1, "G").Value)
                        Else
                        
                            .Cells(i - 1, "H").Value.ClearContents
                            .Cells(i - 1, "I").Value = .Cells(i - 1, "G").Value - Int(.Cells(i - 1, "G").Value)
                        End If
                        
                        .Cells(i - 1, "G").Value = Int(.Cells(i - 1, "G").Value)
                        i = i - 1
                    End If
                ElseIf .Cells(i, "H").Value = "I" Then
                        
                    .Cells(i, "H").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                Else
                
                    .Cells(i, "H").ClearContents
                    .Cells(i, "I").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                End If
            Next i
            
            .Range("G1:I1").Value = Array("Date", "Time In", "Time Out")
            .Columns("G").NumberFormat = "yyyy-mm-dd"
            .Columns("H:I").NumberFormat = "hh:mm AM/PM"
            .Columns("G:I").AutoFit
        End With
        
        Application.ScreenUpdating = True
    End Sub

  5. #5
    HI, Im sorry, I am unfamiliar with macros, should I copy that into my macros somewhere?
    Last edited by Bob Phillips; 2015-02-17 at 04:42 PM. Reason: Unnecessary quote

  6. #6
    .Cells(i - 1, "H").Value.ClearContents
    .Cells(i - 1, "I").Value = .Cells(i - 1, "G").Value - Int(.Cells(i - 1, "G").Value
    )................................................This code is hi-lited when trying to run macros ---runtime error 424 object req
    Last edited by Arnoux; 2015-02-17 at 01:49 PM.

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    It worked in my test data, so perhaps you have something I didn't anticipate. Post your workbook and I will check it out.

  8. #8
    Sample workbook.zipHere is my first 1000 rows

  9. #9
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,829
    Articles
    0
    Excel Version
    O365
    Oops, my error.

    Code:
    Public Sub MergeData()
    Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            .Columns("I").Insert
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = lastrow To 2 Step -1
            
                If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
                
                    If Int(.Cells(i, "G").Value) = Int(.Cells(i - 1, "G").Value) Then
                
                        If .Cells(i, "H").Value = "O" Then
                        
                            .Cells(i - 1, "I").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                            .Cells(i - 1, "H").Value = .Cells(i - 1, "G").Value - Int(.Cells(i - 1, "G").Value)
                            .Cells(i - 1, "G").Value = Int(.Cells(i - 1, "G").Value)
                            .Rows(i).Delete
                            i = i - 1
                        End If
                    Else
                        If .Cells(i, "H").Value = "I" Then
                        
                            .Cells(i, "H").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                        Else
                        
                            .Cells(i, "H").ClearContents
                            .Cells(i, "I").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                        End If
                        .Cells(i, "G").Value = Int(.Cells(i, "G").Value)
                        
                        If .Cells(i - 1, "H").Value = "I" Then
                        
                            .Cells(i - 1, "H").Value = .Cells(i - 1, "G").Value - Int(.Cells(i - 1, "G").Value)
                        Else
                        
                            .Cells(i - 1, "H").ClearContents
                            .Cells(i - 1, "I").Value = .Cells(i - 1, "G").Value - Int(.Cells(i - 1, "G").Value)
                        End If
                        
                        .Cells(i - 1, "G").Value = Int(.Cells(i - 1, "G").Value)
                        i = i - 1
                    End If
                ElseIf .Cells(i, "H").Value = "I" Then
                        
                    .Cells(i, "H").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                Else
                
                    .Cells(i, "H").ClearContents
                    .Cells(i, "I").Value = .Cells(i, "G").Value - Int(.Cells(i, "G").Value)
                End If
            Next i
            
            .Range("G1:I1").Value = Array("Date", "Time In", "Time Out")
            .Columns("G").NumberFormat = "yyyy-mm-dd"
            .Columns("H:I").NumberFormat = "hh:mm AM/PM"
            .Columns("G:I").AutoFit
        End With
        
        Application.ScreenUpdating = True
    End Sub

  10. #10
    It WORKS !!!! thanks alot
    Last edited by Bob Phillips; 2015-02-19 at 10:03 AM. Reason: Unnecessary quote

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •