macro or formulas to lookup by dates

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
483
Office Version
  1. 2019
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
ABCDEFGHI
1
2
3Worker TypeLOBLocationPerson No.DateNameJOBStart TimeEnd Time
401/26/2025apple8:158:45
501/26/2025apple8:1517:45
601/26/2025apple8:1517:45
701/26/2025apple8:1517:45
801/26/2025apple8:1517:45
901/26/2025apple8:1517:45
1001/26/2025apple8:1517:45
1101/26/2025apple8:3018:00
1201/26/2025apple8:3018:00
1301/26/2025apple8:3018:00
1401/26/2025apple8:3018:00
1501/26/2025apple8:3018:00
1601/26/2025apple8:3018:00
1701/26/2025apple8:3018:00
1801/26/2025apple8:3018:00
1901/26/2025apple9:0018:30
2001/26/2025apple9:0018:30
2101/26/2025apple9:0018:30
2201/26/2025apple9:0018:30
2301/26/2025apple9:0018:30
2401/26/2025apple10:1515:30
2501/26/2025apple11:3021:00
2601/26/2025apple12:3022:00
2701/26/2025apple13:1522:45
2801/26/2025apple13:1522:45
2901/26/2025apple13:1522:45
3001/26/2025apple13:1522:45
3101/26/2025apple13:1522:45
3201/26/2025apple13:1522:45
3301/26/2025apple13:1522:45
3401/26/2025apple13:1522:45
3501/26/2025apple13:1522:45
3601/26/2025apple13:1522:45
3701/26/2025apple13:1522:45
3801/26/2025apple13:1522:45
3901/26/2025apple15:3022:15
4001/27/2025peter8:158:45
4101/27/2025peter8:1517:45
4201/27/2025peter8:1517:45
4301/27/2025peter8:1517:45
4401/27/2025peter8:1517:45
4501/27/2025peter8:1517:45
4601/27/2025peter8:1517:45
4701/27/2025peter8:3018:00
4801/27/2025peter8:3018:00
4901/27/2025peter8:3018:00
5001/27/2025peter8:3018:00
5101/27/2025peter8:3018:00
5201/27/2025peter8:3018:00
5301/27/2025peter8:3018:00
5401/27/2025peter8:3018:00
5501/27/2025peter9:0018:30
5601/27/2025peter9:0018:30
5701/27/2025peter9:0018:30
5801/27/2025peter9:0018:30
5901/27/2025peter9:0018:30
6001/27/2025peter10:1515:30
6101/27/2025peter11:3021:00
6201/27/2025peter12:3022:00
6301/27/2025peter13:1522:45
6401/27/2025peter13:1522:45
6501/27/2025peter13:1522:45
6601/27/2025peter13:1522:45
6701/27/2025peter13:1522:45
6801/27/2025peter13:1522:45
6901/27/2025peter13:1522:45
7001/27/2025peter13:1522:45
7101/27/2025peter13:1522:45
7201/27/2025peter13:1522:45
7301/27/2025peter13:1522:45
7401/27/2025peter13:1522:45
7501/27/2025peter15:3022:15
7601/28/2025gary8:158:45
7701/28/2025gary8:1517:45
7801/28/2025gary8:1517:45
7901/28/2025gary8:1517:45
8001/28/2025gary8:1517:45
8101/28/2025gary8:1517:45
8201/28/2025gary8:1517:45
8301/28/2025gary8:3018:00
8401/28/2025gary8:3018:00
8501/28/2025gary8:3018:00
8601/28/2025gary8:3018:00
8701/28/2025gary8:3018:00
8801/28/2025gary8:3018:00
8901/28/2025gary8:3018:00
9001/28/2025gary8:3018:00
9101/28/2025gary9:0018:30
9201/28/2025gary9:0018:30
9301/28/2025gary9:0018:30
9401/28/2025gary9:0018:30
9501/28/2025gary9:0018:30
9601/28/2025gary10:1515:30
9701/28/2025gary11:3021:00
9801/28/2025gary12:3022:00
9901/28/2025gary13:1522:45
10001/28/2025gary13:1522:45
10101/28/2025gary13:1522:45
10201/28/2025gary13:1522:45
10301/28/2025gary13:1522:45
10401/28/2025gary13:1522:45
10501/28/2025gary13:1522:45
10601/28/2025gary13:1522:45
10701/28/2025gary13:1522:45
10801/28/2025gary13:1522:45
10901/28/2025gary13:1522:45
11001/28/2025gary13:1522:45
11101/28/2025gary15:3022:15
list




test.xlsx
ABCDEFGHIJKLMNOP
1
2
3
4
5
6
7
801/26/202501/27/202501/28/202501/29/202501/30/202501/31/202502/01/2025
98:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
108:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
118:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
128:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
138:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
148:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
158:15apple8:15peter8:15gary8:15tom8:00kelvin8:00mary8:00hugo
168:00kelvin8:00mary8:00hugo
178:00kelvin8:00mary8:00hugo
188:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
198:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
208:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
218:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
228:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
238:30apple8:30peter8:30gary8:30tom8:00kelvin8:00mary8:00hugo
248:30apple8:30peter8:30gary8:30tom8:00mary8:00hugo
258:30apple8:30peter8:30gary8:30tom8:00mary8:00hugo
269:00gary8:30kelvin8:00mary8:00hugo
279:00gary8:30kelvin8:00mary8:00hugo
289:00apple9:00peter9:00gary9:00tom8:30kelvin8:00mary8:00hugo
299:00apple9:00peter9:00gary9:00tom8:30kelvin8:00mary8:00hugo
309:00apple9:00peter9:00gary9:00tom8:30kelvin8:00mary8:00hugo
319:00apple9:00peter9:00tom
329:00apple9:00peter9:00tom
3310:15gary9:45kelvin8:30mary8:30hugo
348:30mary8:30hugo
3510:15apple10:15peter10:15tom8:30mary8:30hugo
3611:30gary11:30kelvin8:30mary8:30hugo
378:30mary8:30hugo
3811:30apple11:30peter11:30tom8:30mary8:30hugo
3912:30gary12:30kelvin
4012:30kelvin
4112:30apple12:30peter12:30tom9:45mary9:45hugo
4213:15gary
4313:15gary13:15kelvin
4413:15apple13:15peter13:15gary13:15tom13:15kelvin11:00mary11:00hugo
4513:15apple13:15peter13:15gary13:15tom13:15kelvin11:00mary11:00hugo
4613:15apple13:15peter13:15gary13:15tom13:15kelvin
4713:15apple13:15peter13:15gary13:15tom13:15kelvin
4813:15apple13:15peter13:15gary13:15tom13:15kelvin11:30mary11:30hugo
4913:15apple13:15peter13:15gary13:15tom13:15kelvin
5013:15apple13:15peter13:15gary13:15tom13:15kelvin
5113:15apple13:15peter13:15gary13:15tom13:15kelvin12:30mary12:30hugo
5213:15apple13:15peter13:15gary13:15tom13:15kelvin12:30mary12:30hugo
5313:15apple13:15peter13:15gary13:15tom13:15kelvin12:30mary12:30hugo
5413:15apple13:15peter13:15tom13:15kelvin12:30mary12:30hugo
5513:15apple13:15peter13:15tom13:15kelvin
5615:30gary
5713:15mary13:15hugo
5815:30apple15:30peter15:30tom15:30kelvin13:15mary13:15hugo
5913:15mary13:15hugo
6013:15mary13:15hugo
6113:15mary13:15hugo
6213:15mary13:15hugo
6313:15mary13:15hugo
6413:15mary13:15hugo
6513:15mary13:15hugo
6613:15mary13:15hugo
6713:15mary13:15hugo
6813:15mary13:15hugo
6913:15mary13:15hugo
7013:15mary13:15hugo
7113:15mary13:15hugo
7213:15mary13:15hugo
7313:15mary13:15hugo
7413:15mary13:15hugo
7513:15mary13:15hugo
7613:15mary13:15hugo
7713:15mary13:15hugo
78
79
8015:30mary15:30hugo
81
82
week1




test.xlsx
ABCDEFGHIJKLMNOP
1
2
3
4
5
6
7
802/02/202502/03/202502/04/202502/05/202502/06/202502/07/202502/08/2025
9
10
week2
 
Sort by date Sort by votes
Some of your examples are not consistent with your explanations, but I'll try to guess what you need.

Forget the previous codes and let's start with this one:
VBA Code: 
Sub ExportByDate_v2()
  Dim a As Variant, b As Variant, c 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
  Dim actC As Boolean

  'sort list by date, time, name
  With Sheets("list")
    lr = .Range("E" & Rows.Count).End(3).Row
    .Range("A3:K" & lr).Sort .Range("F3"), xlAscending, .Range("J3"), , xlAscending, .Range("E3"), xlAscending, xlYes
    a = .Range("A4:K" & lr).Value
  End With
  ReDim b(1 To UBound(a, 1) * 3, 1 To 14)       'output first week
  ReDim c(1 To UBound(a, 1) * 3, 1 To 14)       'output second week
  Sheets("week1").Range("C8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("C8:O" & Rows.Count).ClearContents
  
  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 = 2
      n = n + 1
      If n = 8 Then
        j = 1
        actC = True
      Else
        j = j + 2
      End If
      If actC = False Then
        b(1, j + 1) = a(i, 6)       'date
      Else
        c(1, j + 1) = a(i, 6)       'date
      End If
    End If
    
    If InStr(1, a(i, 5), ",") > 0 Then aName = Split(a(i, 5), ",")(0) Else aName = a(i, 5)
    If aTime <> a(i, 10) And k > 2 Then
      k = k + 2
    End If
    
    If actC = False Then
      b(k, j) = a(i, 10)            'start time
      b(k, j + 1) = aName         'name
    Else
      c(k, j) = a(i, 10)            'start time
      c(k, j + 1) = aName         'name
    End If
    aDate = a(i, 6): aTime = a(i, 10)
  Next
  
  Sheets("week1").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Sheets("week2").Range("C8").Resize(UBound(c, 1), UBound(c, 2)).Value = c
End Sub

😇
 

Attachments

  • 1738603694066.png
    1738603694066.png
    21.8 KB · Views: 2
Upvote 0
Solution
DanteAmor said:
Some of your examples are not consistent with your explanations, but I'll try to guess what you need.

Forget the previous codes and let's start with this one:
VBA Code: 
Sub ExportByDate_v2()
  Dim a As Variant, b As Variant, c 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
  Dim actC As Boolean

  'sort list by date, time, name
  With Sheets("list")
    lr = .Range("E" & Rows.Count).End(3).Row
    .Range("A3:K" & lr).Sort .Range("F3"), xlAscending, .Range("J3"), , xlAscending, .Range("E3"), xlAscending, xlYes
    a = .Range("A4:K" & lr).Value
  End With
  ReDim b(1 To UBound(a, 1) * 3, 1 To 14)       'output first week
  ReDim c(1 To UBound(a, 1) * 3, 1 To 14)       'output second week
  Sheets("week1").Range("C8:O" & Rows.Count).ClearContents
  Sheets("week2").Range("C8:O" & Rows.Count).ClearContents
 
  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 = 2
      n = n + 1
      If n = 8 Then
        j = 1
        actC = True
      Else
        j = j + 2
      End If
      If actC = False Then
        b(1, j + 1) = a(i, 6)       'date
      Else
        c(1, j + 1) = a(i, 6)       'date
      End If
    End If
   
    If InStr(1, a(i, 5), ",") > 0 Then aName = Split(a(i, 5), ",")(0) Else aName = a(i, 5)
    If aTime <> a(i, 10) And k > 2 Then
      k = k + 2
    End If
   
    If actC = False Then
      b(k, j) = a(i, 10)            'start time
      b(k, j + 1) = aName         'name
    Else
      c(k, j) = a(i, 10)            'start time
      c(k, j + 1) = aName         'name
    End If
    aDate = a(i, 6): aTime = a(i, 10)
  Next
 
  Sheets("week1").Range("C8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Sheets("week2").Range("C8").Resize(UBound(c, 1), UBound(c, 2)).Value = c
End Sub

😇
Click to expand...

thank you very much for your reply, DanteAmor

your screenshot on gary is my mistake by manually copy & paste

and, this code is real dope as of now unless the name
can this name keep as the original format instead?

1.jpg


thank you very much for your guidance
 
Upvote 0
kelvin_9 said:
can this name keep as the original format instead?
Click to expand...
What are you talking about?
What does the macro say and what do you want as a result?

Don't tell me now that you want the full name instead of the initial name.

If you want the full name, change this line:
VBA Code: 
If InStr(1, a(i, 5), ",") > 0 Then aName = Split(a(i, 5), ",")(0) Else aName = a(i, 5)

For this line:
VBA Code: 
aName = a(i, 5)

🫡
 
Upvote 0
DanteAmor said:
What are you talking about?
What does the macro say and what do you want as a result?

Don't tell me now that you want the full name instead of the initial name.

If you want the full name, change this line:
VBA Code: 
If InStr(1, a(i, 5), ",") > 0 Then aName = Split(a(i, 5), ",")(0) Else aName = a(i, 5)

For this line:
VBA Code: 
aName = a(i, 5)

🫡
Click to expand...
thank you very much for your reply, DanteAmor

my questions get solved as of now

thank you very much again for your patient, guidance
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,226,848
Messages
6,193,315
Members
453,790
Latest member
yassinosnoo1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top