macro or formulas to lookup by dates

[kelvin_9]

greeting to all

i have 14days(7days/week) schedule with over 1000 employees with various working start time and end time each day in sheet1
i am looking for a macro or formula to export first 7days to sheet2 and another 7days to sheet3
also i need 2 empty rows to sepearte each start time for easy reference

i have manually do it on week1

how can i make this happens?

here is my workbookDropbox

your guidance would greatly appreciate

test.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3	Worker Type	LOB	Location	Person No.	Date	Name	JOB	Start Time	End Time
4					01/26/2025	apple		8:15	8:45
5					01/26/2025	apple		8:15	17:45
6					01/26/2025	apple		8:15	17:45
7					01/26/2025	apple		8:15	17:45
8					01/26/2025	apple		8:15	17:45
9					01/26/2025	apple		8:15	17:45
10					01/26/2025	apple		8:15	17:45
11					01/26/2025	apple		8:30	18:00
12					01/26/2025	apple		8:30	18:00
13					01/26/2025	apple		8:30	18:00
14					01/26/2025	apple		8:30	18:00
15					01/26/2025	apple		8:30	18:00
16					01/26/2025	apple		8:30	18:00
17					01/26/2025	apple		8:30	18:00
18					01/26/2025	apple		8:30	18:00
19					01/26/2025	apple		9:00	18:30
20					01/26/2025	apple		9:00	18:30
21					01/26/2025	apple		9:00	18:30
22					01/26/2025	apple		9:00	18:30
23					01/26/2025	apple		9:00	18:30
24					01/26/2025	apple		10:15	15:30
25					01/26/2025	apple		11:30	21:00
26					01/26/2025	apple		12:30	22:00
27					01/26/2025	apple		13:15	22:45
28					01/26/2025	apple		13:15	22:45
29					01/26/2025	apple		13:15	22:45
30					01/26/2025	apple		13:15	22:45
31					01/26/2025	apple		13:15	22:45
32					01/26/2025	apple		13:15	22:45
33					01/26/2025	apple		13:15	22:45
34					01/26/2025	apple		13:15	22:45
35					01/26/2025	apple		13:15	22:45
36					01/26/2025	apple		13:15	22:45
37					01/26/2025	apple		13:15	22:45
38					01/26/2025	apple		13:15	22:45
39					01/26/2025	apple		15:30	22:15
40					01/27/2025	peter		8:15	8:45
41					01/27/2025	peter		8:15	17:45
42					01/27/2025	peter		8:15	17:45
43					01/27/2025	peter		8:15	17:45
44					01/27/2025	peter		8:15	17:45
45					01/27/2025	peter		8:15	17:45
46					01/27/2025	peter		8:15	17:45
47					01/27/2025	peter		8:30	18:00
48					01/27/2025	peter		8:30	18:00
49					01/27/2025	peter		8:30	18:00
50					01/27/2025	peter		8:30	18:00
51					01/27/2025	peter		8:30	18:00
52					01/27/2025	peter		8:30	18:00
53					01/27/2025	peter		8:30	18:00
54					01/27/2025	peter		8:30	18:00
55					01/27/2025	peter		9:00	18:30
56					01/27/2025	peter		9:00	18:30
57					01/27/2025	peter		9:00	18:30
58					01/27/2025	peter		9:00	18:30
59					01/27/2025	peter		9:00	18:30
60					01/27/2025	peter		10:15	15:30
61					01/27/2025	peter		11:30	21:00
62					01/27/2025	peter		12:30	22:00
63					01/27/2025	peter		13:15	22:45
64					01/27/2025	peter		13:15	22:45
65					01/27/2025	peter		13:15	22:45
66					01/27/2025	peter		13:15	22:45
67					01/27/2025	peter		13:15	22:45
68					01/27/2025	peter		13:15	22:45
69					01/27/2025	peter		13:15	22:45
70					01/27/2025	peter		13:15	22:45
71					01/27/2025	peter		13:15	22:45
72					01/27/2025	peter		13:15	22:45
73					01/27/2025	peter		13:15	22:45
74					01/27/2025	peter		13:15	22:45
75					01/27/2025	peter		15:30	22:15
76					01/28/2025	gary		8:15	8:45
77					01/28/2025	gary		8:15	17:45
78					01/28/2025	gary		8:15	17:45
79					01/28/2025	gary		8:15	17:45
80					01/28/2025	gary		8:15	17:45
81					01/28/2025	gary		8:15	17:45
82					01/28/2025	gary		8:15	17:45
83					01/28/2025	gary		8:30	18:00
84					01/28/2025	gary		8:30	18:00
85					01/28/2025	gary		8:30	18:00
86					01/28/2025	gary		8:30	18:00
87					01/28/2025	gary		8:30	18:00
88					01/28/2025	gary		8:30	18:00
89					01/28/2025	gary		8:30	18:00
90					01/28/2025	gary		8:30	18:00
91					01/28/2025	gary		9:00	18:30
92					01/28/2025	gary		9:00	18:30
93					01/28/2025	gary		9:00	18:30
94					01/28/2025	gary		9:00	18:30
95					01/28/2025	gary		9:00	18:30
96					01/28/2025	gary		10:15	15:30
97					01/28/2025	gary		11:30	21:00
98					01/28/2025	gary		12:30	22:00
99					01/28/2025	gary		13:15	22:45
100					01/28/2025	gary		13:15	22:45
101					01/28/2025	gary		13:15	22:45
102					01/28/2025	gary		13:15	22:45
103					01/28/2025	gary		13:15	22:45
104					01/28/2025	gary		13:15	22:45
105					01/28/2025	gary		13:15	22:45
106					01/28/2025	gary		13:15	22:45
107					01/28/2025	gary		13:15	22:45
108					01/28/2025	gary		13:15	22:45
109					01/28/2025	gary		13:15	22:45
110					01/28/2025	gary		13:15	22:45
111					01/28/2025	gary		15:30	22:15
list

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6
7
8			01/26/2025		01/27/2025		01/28/2025		01/29/2025		01/30/2025		01/31/2025		02/01/2025
9		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
10		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
11		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
12		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
13		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
14		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
15		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
16										8:00	kelvin	8:00	mary	8:00	hugo
17										8:00	kelvin	8:00	mary	8:00	hugo
18		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
19		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
20		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
21		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
22		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
23		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
24		8:30	apple	8:30	peter	8:30	gary	8:30	tom			8:00	mary	8:00	hugo
25		8:30	apple	8:30	peter	8:30	gary	8:30	tom			8:00	mary	8:00	hugo
26						9:00	gary			8:30	kelvin	8:00	mary	8:00	hugo
27						9:00	gary			8:30	kelvin	8:00	mary	8:00	hugo
28		9:00	apple	9:00	peter	9:00	gary	9:00	tom	8:30	kelvin	8:00	mary	8:00	hugo
29		9:00	apple	9:00	peter	9:00	gary	9:00	tom	8:30	kelvin	8:00	mary	8:00	hugo
30		9:00	apple	9:00	peter	9:00	gary	9:00	tom	8:30	kelvin	8:00	mary	8:00	hugo
31		9:00	apple	9:00	peter			9:00	tom
32		9:00	apple	9:00	peter			9:00	tom
33						10:15	gary			9:45	kelvin	8:30	mary	8:30	hugo
34												8:30	mary	8:30	hugo
35		10:15	apple	10:15	peter			10:15	tom			8:30	mary	8:30	hugo
36						11:30	gary			11:30	kelvin	8:30	mary	8:30	hugo
37												8:30	mary	8:30	hugo
38		11:30	apple	11:30	peter			11:30	tom			8:30	mary	8:30	hugo
39						12:30	gary			12:30	kelvin
40										12:30	kelvin
41		12:30	apple	12:30	peter			12:30	tom			9:45	mary	9:45	hugo
42						13:15	gary
43						13:15	gary			13:15	kelvin
44		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin	11:00	mary	11:00	hugo
45		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin	11:00	mary	11:00	hugo
46		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin
47		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin
48		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin	11:30	mary	11:30	hugo
49		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin
50		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin
51		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin	12:30	mary	12:30	hugo
52		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin	12:30	mary	12:30	hugo
53		13:15	apple	13:15	peter	13:15	gary	13:15	tom	13:15	kelvin	12:30	mary	12:30	hugo
54		13:15	apple	13:15	peter			13:15	tom	13:15	kelvin	12:30	mary	12:30	hugo
55		13:15	apple	13:15	peter			13:15	tom	13:15	kelvin
56						15:30	gary
57												13:15	mary	13:15	hugo
58		15:30	apple	15:30	peter			15:30	tom	15:30	kelvin	13:15	mary	13:15	hugo
59												13:15	mary	13:15	hugo
60												13:15	mary	13:15	hugo
61												13:15	mary	13:15	hugo
62												13:15	mary	13:15	hugo
63												13:15	mary	13:15	hugo
64												13:15	mary	13:15	hugo
65												13:15	mary	13:15	hugo
66												13:15	mary	13:15	hugo
67												13:15	mary	13:15	hugo
68												13:15	mary	13:15	hugo
69												13:15	mary	13:15	hugo
70												13:15	mary	13:15	hugo
71												13:15	mary	13:15	hugo
72												13:15	mary	13:15	hugo
73												13:15	mary	13:15	hugo
74												13:15	mary	13:15	hugo
75												13:15	mary	13:15	hugo
76												13:15	mary	13:15	hugo
77												13:15	mary	13:15	hugo
78
79
80												15:30	mary	15:30	hugo
81
82
week1

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6
7
8			02/02/2025		02/03/2025		02/04/2025		02/05/2025		02/06/2025		02/07/2025		02/08/2025
9
10
week2

 

[DanteAmor]

Try this:

Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
  
  aDate = a(1, 5):  aName = a(1, 6):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 5) Then
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 5)
    End If
    If aName <> a(i, 6) Or aTime <> a(i, 8) Then k = k + 2
    
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 6)
    aDate = a(i, 5): aName = a(i, 6): aTime = a(i, 8)
  Next
  
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[kelvin_9]

Try this:

Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
 
  aDate = a(1, 5):  aName = a(1, 6):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 5) Then
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 5)
    End If
    If aName <> a(i, 6) Or aTime <> a(i, 8) Then k = k + 2
  
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 6)
    aDate = a(i, 5): aName = a(i, 6): aTime = a(i, 8)
  Next
 
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

thank you very much for your reply, DanteAmor

THIS IS REAL DOPE AND IMPECCABLE!!!!!

however:

1) the code can run exactly what i want but i found when i put this code to my workbook(obviously not only one employee per day)
it turns out sepearted every start time like below screenshot

test.xlsx
	A	B	C	D	E	F	G	H	I
3	Worker Type	LOB	Location	Person No.	Date	Name	JOB	Start Time	End Time
4					01/26/2025	apple		8:15	8:45
5					01/26/2025	peter		8:15	17:45
6					01/26/2025	apple		8:15	17:45
7					01/26/2025	peter		8:15	17:45
8					01/26/2025	apple		8:15	17:45
9					01/26/2025	peter		8:15	17:45
10					01/26/2025	apple		8:15	17:45
11					01/26/2025	peter		8:30	18:00
12					01/26/2025	apple		8:30	18:00
13					01/26/2025	peter		8:30	18:00
14					01/26/2025	apple		8:30	18:00
15					01/26/2025	peter		8:30	18:00
16					01/26/2025	apple		8:30	18:00
list

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
8			01/26/2025		01/27/2025		01/28/2025		01/29/2025		01/30/2025		01/31/2025		02/01/2025
9		8:15	apple	8:15	apple	8:15	apple	8:15	apple	8:00	apple	8:00	apple	8:00	apple
10
11
12		8:15	peter	8:15	peter	8:15	peter	8:15	peter	8:00	peter	8:00	peter	8:00	peter
13
14
15		8:15	apple	8:15	apple	8:15	apple	8:15	apple	8:00	apple	8:00	apple	8:00	apple
16
17
18		8:15	peter	8:15	peter	8:15	peter	8:15	peter	8:00	peter	8:00	peter	8:00	peter
19
20
21		8:15	apple	8:15	apple	8:15	apple	8:15	apple	8:00	apple	8:00	apple	8:00	apple
22
23
24		8:15	peter	8:15	peter	8:15	peter	8:15	peter	45687.333	peter	8:00	peter	8:00	peter
25
26
27		8:15	apple	45684.34	apple	8:15	apple	45686.344	apple	8:00	apple	8:00	apple	8:00	apple
28
week1

2) my tech team swapped the 2 columns on col E and col F
col E - date > col F
col F - name > col E
can you show me how do i amend your code?

thank you very much for you guidance and support

 

[DanteAmor]

it turns out sepearted every start time

This is solved if before running the macro, you sort your data by Date, Name and start time.

2) my tech team swapped the 2 columns on col E and col F
col E - date > col F
col F - name > col E
can you show me how do i amend your code?

I marked the changes in blue:

Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
  
  aDate = a(1, 6):  aName = a(1, 5):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(i, 8) Then k = k + 2
    
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 5)         'name
    aDate = a(i, 6): aName = a(i, 5): aTime = a(i, 8)
  Next
  
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[kelvin_9]

This is solved if before running the macro, you sort your data by Date, Name and start time.




I marked the changes in blue:

Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
 
  aDate = a(1, 6):  aName = a(1, 5):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(i, 8) Then k = k + 2
   
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 5)         'name
    aDate = a(i, 6): aName = a(i, 5): aTime = a(i, 8)
  Next
 
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

thank you very much for your reply, DanteAmo

1) same outcome after sort name, date, start time. i was thinking, is it the name comes with "initial + full name" casued the issue?
ie. Summie, Chan Chi Ming

2) like your changes in blue, if the strat time change to anoter column(example J)
am i simply change like this in red?

Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
 
  aDate = a(1, 6):  aName = a(1, 5):  aTime = a(1, 10)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(1, 10) Then k = k + 2
   
    b(k, j) = a(1, 10)
    b(k, j + 1) = a(i, 5)         'name
    aDate = a(i, 6): aName = a(i, 5): aTime = a(1, 10)
  Next
 
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

thank you very much for you guidance and support

 

[kelvin_9]

This is solved if before running the macro, you sort your data by Date, Name and start time.




I marked the changes in blue:

Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:I" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 14)
  Sheets("week1").Range("B8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("B8:O" & Rows.Count).ClearContents
 
  aDate = a(1, 6):  aName = a(1, 5):  aTime = a(1, 8)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(i, 8) Then k = k + 2
   
    b(k, j) = a(i, 8)
    b(k, j + 1) = a(i, 5)         'name
    aDate = a(i, 6): aName = a(i, 5): aTime = a(i, 8)
  Next
 
  Sheets("week2").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

Hi DanteAmor

i amened the code like below, but still can not fix the outcome even sort before run the code by your suggestion
example. Summie, Chan Chi Ming

Sub ExportByDate()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long

  a = Sheets("list").Range("A4:k" & Sheets("list").Range("E" & Rows.Count).End(3).Row).Value
  ReDim C(1 To UBound(a, 1), 1 To 14)
  Sheets("week2").Range("c8:p" & Rows.Count).ClearContents
  
  aDate = a(1, 6):  aName = a(1, 5):  aTime = a(1, 10)
  C(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("C8").Resize(UBound(C, 1), UBound(C, 2)).Value = C
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      C(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(i, 10) Then k = k + 2
    
    C(k, j) = a(i, 10)
    C(k, j + 1) = a(i, 5)         'name
    aDate = a(i, 6): aName = a(i, 5): aTime = a(i, 10)
  Next
  
  Sheets("week2").Range("C8").Resize(UBound(C, 1), UBound(C, 2)).Value = C
End Sub

 

[DanteAmor]

I don't understand why you set those values.
You can put the minisheets here so that I can see how the data really is and what the result you want is.

 

[kelvin_9]

View attachment 121920
I don't understand why you set those values.
You can put the minisheets here so that I can see how the data really is and what the result you want is.

Hi DanteAmor
thank you very much for your reply

please allow me to start it over and please forgive my presentation as my english is not mother tone

#1) my workbook has 3 sheets so far, "list", "week1", and "week2", this is the lastest version updated by my tech team
#2) i need a macro to run the first 7days into "week1" , and next 7days into "week2" from "list"
#3) i need 2 empty rows to sepearte each start time for easy reference
#4) just to pay safe, i want to start at column C instead of column B on "week1" and "week2"
#5) the name format in column E is "initial, full name" (example: Summie, Chan Chi Ming), but i can't return like you, even i use

=LEFT(A2, FIND(",", A2)-1)

to extract and run only by initial, i got the same result
#6) your coding in #2 was brillant, but i get stucks when i change those names back to my employee's name format, even though you said sort by "name, date, and start time" first, it still breaks 2 rows for every start time

thank you very much for your guidance again and again

Store List (0209-0222).xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	HKDL Schedule List
2	671078 1/22/2025 5:57:46 PM
3	Worker Type	LOB	Location	Person No.	Name	Date	JOB	WBS	Workrule	Start Time	End Time
4					Ally	02/09/2025				8:15	17:45
5					Carol	02/09/2025				8:15	17:45
6					Catherine	02/09/2025				8:15	17:45
7					Kelvin	02/09/2025				8:15	17:45
8					Nicole	02/09/2025				8:15	17:45
9					Summie	02/09/2025				8:15	8:45
10					Yuki	02/09/2025				8:15	17:45
11					Amy	02/09/2025				8:30	18:00
12					Andy	02/09/2025				8:30	18:00
13					Ken	02/09/2025				8:30	18:00
14					Mavis	02/09/2025				8:30	18:00
15					Nickey	02/09/2025				8:30	18:00
16					Selly	02/09/2025				8:30	18:00
17					Sharon	02/09/2025				8:30	18:00
18					Sunnie	02/09/2025				8:30	18:00
19					Tracy	02/09/2025				8:30	18:00
20					Emily	02/09/2025				9:00	18:30
21					Jessica	02/09/2025				9:00	18:30
22					Kelly	02/09/2025				9:00	18:30
23					Dennis	02/09/2025				9:15	18:45
24					Holiday	02/09/2025				9:15	18:45
25					Summie	02/09/2025				10:15	15:30
26					Yumi	02/09/2025				12:30	22:00
27					Zarlish	02/09/2025				12:30	22:00
28					Bowie	02/09/2025				13:15	22:45
29					Chocolate	02/09/2025				13:15	22:45
30					Derrick	02/09/2025				13:15	22:45
31					Icy	02/09/2025				13:15	22:45
32					Jesse	02/09/2025				13:15	22:45
33					Kali	02/09/2025				13:15	22:45
34					Kin	02/09/2025				13:15	22:45
list

 

[DanteAmor]

#5) the name format in column E is "initial, full name" (example: Summie, Chan Chi Ming), but i can't return like you, even i use
to extract and run only by initial, i got the same result

I don't understand that part. But I'll try.

So first run this version:

Sub ExportByDate_v1()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long, lr As Long

  'sort list by date, time, name
  With Sheets("list")
    lr = .Range("E" & Rows.Count).End(3).Row
    With .Range("A4:K" & lr)
      .Sort .Range("F3"), xlAscending, .Range("J3"), , xlAscending, .Range("E3"), xlAscending, xlYes
      a = .Value
    End With
  End With
  ReDim b(1 To UBound(a, 1) * 3, 1 To 14)
  Sheets("week1").Range("C8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("C8:O" & Rows.Count).ClearContents
  
  aName = a(1, 5): aDate = a(1, 6): aTime = a(1, 10)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(i, 10) Then k = k + 2
    
    b(k, j) = a(i, 10)            'start time
    b(k, j + 1) = a(i, 5)         'name
    aName = a(i, 5): aDate = a(i, 6): aTime = a(i, 10)
  Next
  
  If n < 8 Then
    Sheets("week1").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Else
    Sheets("week2").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End If
End Sub

Version with initial name:

Sub ExportByDate_v2()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant, auxName As String
  Dim i As Long, j As Long, k As Long, n As Long, lr As Long

  'sort list by date, time, name
  With Sheets("list")
    lr = .Range("E" & Rows.Count).End(3).Row
    With .Range("A4:K" & lr)
      .Sort .Range("F3"), xlAscending, .Range("J3"), , xlAscending, .Range("E3"), xlAscending, xlYes
      a = .Value
    End With
  End With
  ReDim b(1 To UBound(a, 1) * 3, 1 To 14)
  Sheets("week1").Range("C8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("C8:O" & Rows.Count).ClearContents
  
  If InStr(1, a(1, 5), ",") > 0 Then aName = Split(a(1, 5), ",")(0) Else aName = a(1, 5)
  aDate = a(1, 6): aTime = a(1, 10)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    
    If InStr(1, a(i, 5), ",") > 0 Then auxName = Split(a(i, 5), ",")(0) Else auxName = a(i, 5)
    If aName <> auxName Or aTime <> a(i, 10) Then k = k + 2
    
    b(k, j) = a(i, 10)            'start time
    b(k, j + 1) = auxName         'name
    aName = auxName: aDate = a(i, 6): aTime = a(i, 10)
  Next
  
  If n < 8 Then
    Sheets("week1").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Else
    Sheets("week2").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End If
End Sub

 

[kelvin_9]

I don't understand that part. But I'll try.

So first run this version:

Sub ExportByDate_v1()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant
  Dim i As Long, j As Long, k As Long, n As Long, lr As Long

  'sort list by date, time, name
  With Sheets("list")
    lr = .Range("E" & Rows.Count).End(3).Row
    With .Range("A4:K" & lr)
      .Sort .Range("F3"), xlAscending, .Range("J3"), , xlAscending, .Range("E3"), xlAscending, xlYes
      a = .Value
    End With
  End With
  ReDim b(1 To UBound(a, 1) * 3, 1 To 14)
  Sheets("week1").Range("C8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("C8:O" & Rows.Count).ClearContents
 
  aName = a(1, 5): aDate = a(1, 6): aTime = a(1, 10)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
    If aName <> a(i, 5) Or aTime <> a(i, 10) Then k = k + 2
   
    b(k, j) = a(i, 10)            'start time
    b(k, j + 1) = a(i, 5)         'name
    aName = a(i, 5): aDate = a(i, 6): aTime = a(i, 10)
  Next
 
  If n < 8 Then
    Sheets("week1").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Else
    Sheets("week2").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End If
End Sub

Version with initial name:

Sub ExportByDate_v2()
  Dim a As Variant, b As Variant, aDate As Variant, aName As Variant, aTime As Variant, auxName As String
  Dim i As Long, j As Long, k As Long, n As Long, lr As Long

  'sort list by date, time, name
  With Sheets("list")
    lr = .Range("E" & Rows.Count).End(3).Row
    With .Range("A4:K" & lr)
      .Sort .Range("F3"), xlAscending, .Range("J3"), , xlAscending, .Range("E3"), xlAscending, xlYes
      a = .Value
    End With
  End With
  ReDim b(1 To UBound(a, 1) * 3, 1 To 14)
  Sheets("week1").Range("C8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("C8:O" & Rows.Count).ClearContents
 
  If InStr(1, a(1, 5), ",") > 0 Then aName = Split(a(1, 5), ",")(0) Else aName = a(1, 5)
  aDate = a(1, 6): aTime = a(1, 10)
  b(1, 2) = aDate
  k = 1:  j = 1:  n = 1
  For i = 1 To UBound(a)
    k = k + 1
    If aDate <> a(i, 6) Then      'date
      k = 0
      n = n + 1
      If n = 8 Then
        j = 1
        Sheets("week1").Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        ReDim b(1 To UBound(a, 1), 1 To 14)
      Else
        j = j + 2
      End If
      b(1, j + 1) = a(i, 6)       'date
    End If
   
    If InStr(1, a(i, 5), ",") > 0 Then auxName = Split(a(i, 5), ",")(0) Else auxName = a(i, 5)
    If aName <> auxName Or aTime <> a(i, 10) Then k = k + 2
   
    b(k, j) = a(i, 10)            'start time
    b(k, j + 1) = auxName         'name
    aName = auxName: aDate = a(i, 6): aTime = a(i, 10)
  Next
 
  If n < 8 Then
    Sheets("week1").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Else
    Sheets("week2").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End If
End Sub

after the first code, below is part of the result on "week1"
-the code start at column B, i need it at column C
-every 2 empty rows for each employee but not each start time, i dont need empty rows between every employee

MSS Store List (0209-0222).xls
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8			9/2/2025		10/2/2025		11/2/2025
9		45697.34	Summie, Hx Wxx Txx	45698.34	Carol, Kxx Wxx Hxx	45699.34	Derrick, Cxx Kxx Cxx
10
11
12		45697.34	Ally, Cxx HxxMxx	45698.34	James, Fxx Cxx Kxx	45699.34	Lina, Bxx Pxx
13
14
15		45697.34	Carol, Kxx Wxx Hxx	45698.34	Jennifer, Txx Kxx Lxx	45699.34	Ming, Wxx Cxx Mxx
week1

after the first code, below is part of the result on "week2"
-every 2 empty rows for each employee but not each start time, i dont need empty rows between every employee

MSS Store List (0209-0222).xls
	A	B	C	D	E	F	G	H
1
2
3
4
5
6
7
8				16/2/2025		17/2/2025		18/2/2025
9			45704.34	Howard, Cxx Hxx Cxx	45705.34	Andy, Cxx Yxx Hxx	45706.34	Andy, Cxx Yxx Hxx
10
11
12			45704.34	Kin, Nxx Yxx Lxx	45705.34	Chloe, Cxx Cxx Cxx	45706.34	Carol, Kxx Wxx Hxx
13
14
15			45704.34	Margaret, Txx Yxx Cxx	45705.34	Hiko, Lxx Pxx Pxx	45706.34	Derrick, Cxx Kxx Cxx
week2

after the second code, below is part of the result on "week1", same as "week2"
-the first code with the "full name" is good, can i keep this?

MSS Store List (0209-0222).xls
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7
8			9/2/2025		10/2/2025		11/2/2025		12/2/2025
9		45697.34	Summie	45698.34	Carol	45699.34	Derrick	45700.34	Ally
10
11
12		45697.34	Ally	45698.34	James	45699.34	Lina	45700.34	Chocolate
13
14
15		45697.34	Carol	45698.34	Jennifer	45699.34	Ming	45700.34	Ivy
16
17
18		45697.34	Catherine	45698.34	Lina	45699.34	Rainbow	45700.34	Thetis
19
20
21		45697.34	Kelvin	45698.34	Nickey	45699.34	Sophia	45700.34	Toru
22
23
24		45697.34	Nicole	45698.34	Shirley	45699.34	Walter	45700.34	Wynter
25
26
27		45697.34	Yuki	45698.34	Tracy	45699.34	Winnie	45700.34	Yuki
week1

-can i return the result like this?

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5
6
7
8			01/26/2025		01/27/2025		01/28/2025		01/29/2025		01/30/2025		01/31/2025		02/01/2025
9		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
10		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
11		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
12		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
13		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
14		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
15		8:15	apple	8:15	peter	8:15	gary	8:15	tom	8:00	kelvin	8:00	mary	8:00	hugo
16										8:00	kelvin	8:00	mary	8:00	hugo
17										8:00	kelvin	8:00	mary	8:00	hugo
18		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
19		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
20		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
21		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
22		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
23		8:30	apple	8:30	peter	8:30	gary	8:30	tom	8:00	kelvin	8:00	mary	8:00	hugo
24		8:30	apple	8:30	peter	8:30	gary	8:30	tom			8:00	mary	8:00	hugo
25		8:30	apple	8:30	peter	8:30	gary	8:30	tom			8:00	mary	8:00	hugo
26						9:00	gary			8:30	kelvin	8:00	mary	8:00	hugo
27						9:00	gary			8:30	kelvin	8:00	mary	8:00	hugo
28		9:00	apple	9:00	peter	9:00	gary	9:00	tom	8:30	kelvin	8:00	mary	8:00	hugo
29		9:00	apple	9:00	peter	9:00	gary	9:00	tom	8:30	kelvin	8:00	mary	8:00	hugo
30		9:00	apple	9:00	peter	9:00	gary	9:00	tom	8:30	kelvin	8:00	mary	8:00	hugo
31		9:00	apple	9:00	peter			9:00	tom
32		9:00	apple	9:00	peter			9:00	tom
33						10:15	gary			9:45	kelvin	8:30	mary	8:30	hugo
34												8:30	mary	8:30	hugo
35		10:15	apple	10:15	peter			10:15	tom			8:30	mary	8:30	hugo
week1