VBA macro for transposing row and columns based on one condition

nn992

New Member
Joined
Jul 28, 2016
Messages
47
I am trying to manage time sheet of my company and have a small issue. I have data ordered in rows per person, however I would like to have them in column format. Currently, data is in following format:




I would like it to have the following shape:
Rules that are to be followed: withing the same name(person) and same date, take the "OUT" time value and put it to the next row. Please note that there are few thousand rows.


Questions are very welcome Many thanks in advance!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,906
Office Version
  1. 365
Platform
  1. Windows
Try this VBA code:
Code:
Sub MyMacro()

    Dim lr As Long
    
'   Find last row with data in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row

'   Put header on column E
    Range("E1") = "Out"

'   Copy values from column C to column E, shifting up one row
    Range("C3:C" & lr).Copy Range("E2")
    
'   Removes "OUT" values in column D
    Application.DisplayAlerts = False
    Columns("D:D").Replace What:="OUT", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.DisplayAlerts = True
    
'   Remove rows with blanks in column D
    Range("D1:D" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
'   Remove column D
    Columns("D:D").Delete
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,823
Members
410,813
Latest member
Vhinzvirgo
Top