VBA - consolidating list

[gd6noob]

Hi there,

I have a report, its a list of employee punches that I want to consolidate. Theres a new row for each punch, which I dont want.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	BALANCE DATE	FIRST NAME	LAST NAME	EMPLOYEE ID	SHIFT	FCLM AREA	Manager ID	PUNCH DATE	PUNCH TIME	SITEID	USERNAME	SERVER	SOURCE SYSTEM
2	03/12/2021			102989383	DH5C0700	3 -	109209241	03/12/2021	06:51:00 AM
3	03/12/2021			109064517	DC4C0700	3 -	106928187	03/12/2021	07:03:00 AM
4	03/12/2021			105016027	DB0CACOM	3 -	109209241	03/12/2021	07:08:00 AM
5	03/12/2021			109399457	DB3C0700	3 -	109209241	03/12/2021	07:00:00 AM
6	03/12/2021			109154451	DB3C0700	3 -	109209241	03/12/2021	07:00:00 AM
Sheet1

I would like a code so that if it finds the same employee ID, it would put the next Punch Time into Punch Time 2, and if it finds another, into Punch Time 3 and so forth.

Book1
	A	B	C	D	E	F	G
1	EMPLOYEE ID	PUNCH TIME 1	PUNCH TIME 2	PUNCH TIME 3	PUNCH TIME 4	PUNCH TIME 5	PUNCH TIME 6
2
3
4
5
Sheet2

There would be approximately 10,000+ rows, not sure if excel has trouble handling such high volume data and if it would take long?

 

[mart37]

Sub PunchTime()
    Dim shSrc As Worksheet
    Dim shDest As Worksheet
    Dim rngSrc As Range
    Dim rngDest As Range
    Dim lr As Long
    Dim lc As Integer
    Dim c As Range
    Dim sEmp As Variant
    Dim tEmp As Date
    Set shSrc = Worksheets("Sheet1")
    Set shDest = Worksheets("Sheet2")
    lrSrc = shSrc.Range("D" & Rows.Count).End(xlUp).Row
    Set rngSrc = shSrc.Range("D2:D" & lrSrc)
    For Each c In rngSrc
        sEmp = c.Value
        tEmp = c.Offset(0, 5)
        lrDest = shDest.Range("A" & Rows.Count).End(xlUp).Row
        Set rngDest = shDest.Range("A1:A" & lrDest)
        fr = Application.Match(sEmp, rngDest, 0)
        If Not IsError(fr) Then
            lc = shDest.Cells(fr, Columns.Count).End(xlToLeft).Column + 1
            shDest.Cells(fr, lc) = tEmp
        Else
            shDest.Cells(lrDest + 1, 1) = sEmp
            shDest.Cells(lrDest + 1, 2) = tEmp
        End If
    Next
End Sub

 

[gd6noob]

Hi there,

Thank you for the quick response, I tried the above code but it is not quite what I want.
The output is still in the column. If possible to have the next punch for the same employee in the next column, which is C and if there is a 3rd or 4th punch, in their respective columns, D and E.

Book1
	A	B	C	D	E	F	G
1	EMPLOYEE ID	PUNCH TIME 1	PUNCH TIME 2	PUNCH TIME 3	PUNCH TIME 4	PUNCH TIME 5	PUNCH TIME 6
2	106558958	4:16:00 PM
3	106558958	7:37:00 AM
Sheet2

 

[mart37]

The macro can't match because the datatype in column A must be the same as column D.
What is the datatype of both columns: number or text or ....??
You can change this line: Dim sEmp As Variant --> Dim sEmp As Long

 

[igold]

Is column A "Balance Date" significant or do you just want all the same employee number to have their "Punch Time" to run across the columns.

 

[gd6noob]

In this case, date would not matter. Having their punch times in the same row is the main part.

Thank you

 

[gd6noob]

The macro can't match because the datatype in column A must be the same as column D.
What is the datatype of both columns: number or text or ....??
You can change this line: Dim sEmp As Variant --> Dim sEmp As Long

Thank you, this worked well..