Large Data Sets Into Schedules

davidmel20

New Member
Joined
Feb 28, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi, I tried a lot of things for this but unfortunately I'm not well-versed enough in VBA to get what I need done and couldn't find any other related posts, so I'm coming to the experts! :)

I have a data set that includes 20 or so columns but I only need to reference three fields, "Agent", "ID #" and "Ticket Scheduled Date" (In the normal report, these will not be in columns A, B and C as they are in the example). There are a few things that I'm trying to make happen...

I've attached a few images of the data and what the end result would look like.
1) Based on the values that I put in sheet "Schedule", cells D1 and G1, create dates going across beginning in C5 until the date that's populated in G1.
2) Populate column B ("Agent Name") and the corresponding ID number relative to the scheduled date across the top. The data for this is in sheet "Data". If there is someone in the "Data" sheet that doesn't have a ticket scheduled within the dates that I've populated, I don't want them to show up. Some agents may have multiple ticket ID's for one day also.
3) Based on the value of the ID #, the color coding needs to change (over 600000 vs under 600000). I'm aware this can be done with conditional formatting but I'm not sure if it would be easier to just include in a macro.

I'm looking to make this as simple as possible, probably via a button or msg box, as it will be for entry-level employees to update the "Data" tab and then run the updates. Any help is greatly appreciated and thank you in advance.
 

Attachments

  • Data Example.PNG
    Data Example.PNG
    56.5 KB · Views: 11
  • Schedule Example.PNG
    Schedule Example.PNG
    60 KB · Views: 11

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

For the future, I would strongly recommend providing any sample data using XL2BB as helpers are reluctant to spend a lot of time manually typing out sample data to test with. ;)

Anyway, give this a try in a copy of your workbook to see if it is close to the mark.

VBA Code:
Sub MakeSchedule()
  Dim a As Variant
  Dim StartDate As Date, Enddate As Date
  Dim NumDays As Long, c As Long, r As Long, i As Long
  
  a = Sheets("Data").Range("A2", Sheets("Data").Range("C" & Rows.Count).End(xlUp)).Value
  With Sheets("Schedule")
    .UsedRange.Offset(4).Clear
    StartDate = .Range("D1").Value
    Enddate = .Range("G1").Value
    NumDays = Enddate - StartDate + 1
    .Cells(5, 2).Value = "Agent Name"
    For c = 0 To NumDays - 1
      .Cells(5, 3 + c).Value = StartDate + c
    Next c
    r = 5
    For i = 1 To UBound(a)
      If a(i, 3) >= StartDate And a(i, 3) <= Enddate Then
        c = a(i, 3) - StartDate
        r = r + 1
        .Cells(r, 2).Value = a(i, 1)
        With .Cells(r, 3 + c)
          .Value = a(i, 2)
          .Interior.ColorIndex = IIf(a(i, 2) > 600000, 40, 50)
        End With
      End If
    Next i
  End With
End Sub
 

davidmel20

New Member
Joined
Feb 28, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
This is PERFECT! I updated the code below to make a few minor changes, adding a few columns with more data (customer, location, revenue and status). Is there any chance we can add these in? I tried to add these fields in myself to pull from the ticket ID but am having trouble with filling down the correct number of rows, I basically just added in a vlookup which probably isn't the ideal way to go. I've also put in the XL2BB :). These are the final changes that I'm hoping to add in:
-Add "Customer", "Location", "Revenue $" and "Status" from Data sheet based off of the ticket ID
-Format the column width to 11 only for the added dates across row 5 (F5:5)
-For all values in row 5, format the font the same way (blue fill & bold)

VBA Code:
Sub MakeSchedule()
  Dim a As Variant
  Dim StartDate As Date, Enddate As Date
  Dim NumDays As Long, c As Long, r As Long, i As Long
  
  a = Sheets("Data").Range("A2", Sheets("Data").Range("C" & Rows.Count).End(xlUp)).Value
  With Sheets("Schedule")
    .UsedRange.Offset(4).Clear
    StartDate = .Range("C1").Value
    Enddate = .Range("F1").Value
    NumDays = Enddate - StartDate + 1
    .Cells(5, 1).Value = "Agent Name"
    .Cells(5, 2).Value = "Customer"
    .Cells(5, 3).Value = "Location"
    .Cells(5, 4).Value = "Revenue $"
    .Cells(5, 5).Value = "Status"
    For c = 0 To NumDays - 1
      .Cells(5, 6 + c).Value = StartDate + c
    Next c
    r = 5
    For i = 1 To UBound(a)
      If a(i, 3) >= StartDate And a(i, 3) <= Enddate Then
        c = a(i, 3) - StartDate
        r = r + 1
        .Cells(r, 1).Value = a(i, 1)
        With .Cells(r, 6 + c)
          .Value = a(i, 2)
          .Interior.ColorIndex = 40
        End With
      End If
    Next i
  End With
End Sub

mrexelmacro_w customer.xlsm
ABCDEFG
1Agent NameID #Ticket Scheduled DateCustomerLocation Revenue $ Status
2Max Brown4791562/28/2020Customer ASITE 123 $ 3,000.00 Schedule Due
3Janet Black4372182/29/2020Customer BSITE 356 $ 5,000.00 Assigned
4David White3334933/1/2020Customer CSITE 722 $ 8,000.00 In Process
5Bob Purple486878Customer DSITE 843 $ 2,000.00 Schedule Due
6Karen Blue4523882/28/2020Customer ESITE 223 $ 4,500.00 Assigned
7John Purple584910Customer FSITE 128 $ 8,700.00 In Process
8Adrian Aqua4301053/4/2020Customer GSITE 169 $ 9,300.00 Schedule Due
9Adrian Aqua9463313/4/2020Customer HSITE 453 $ 1,600.00 Assigned
10Phil Magenta284020Customer ISITE 954 $ 8,300.00 In Process
11Carrie Green1878103/6/2020Customer JSITE 100 $ 5,600.00 Assigned
Data


mrexelmacro_w customer.xlsm
ABCDEFGHIJKL
1Schedule Start Date:2/27/2020Schedule End Date:3/4/2020
2
3
4TICKET SCHEDULED DATE
5Agent NameCustomerLocationRevenue $Status2/27/20202/28/20202/29/20203/1/20203/2/20203/3/20203/4/2020
6Max Brown479156
7Janet Black437218
8David White333493
9Karen Blue452388
10Adrian Aqua430105
11Adrian Aqua946331
Schedule
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Thanks for using XL2BB - makes things much easier. :)

Give this version a try.
Notes:
  1. The code does not alter the column widths so set them manually how you want (eg columns F:?? to width 11) & they should remain unchanged.
  2. You didn't mention changing the two-tone colour arrangement though I notice your code has reverted to a single colour. I have left the 2-colour arrangement in place (though implemented a little differently).
VBA Code:
Sub MakeSchedule_v2()
  Dim a As Variant
  Dim StartDate As Date, Enddate As Date
  Dim NumDays As Long, c As Long, r As Long, i As Long
 
  a = Sheets("Data").Range("A2", Sheets("Data").Range("G" & Rows.Count).End(xlUp)).Value
  With Sheets("Schedule")
    .UsedRange.Offset(5).Clear
    .UsedRange.Offset(4, 6).Clear
    StartDate = .Range("C1").Value
    Enddate = .Range("F1").Value
    NumDays = Enddate - StartDate + 1
    With .Range("F5").Resize(, NumDays)
      .Cells(1).Copy Destination:=.Cells(1).Resize(, NumDays)
      .Value = Application.Transpose(Evaluate("Row(" & CLng(StartDate) & ":" & CLng(StartDate) + NumDays - 1 & ")"))
    End With
    r = 5
    For i = 1 To UBound(a)
      If a(i, 3) >= StartDate And a(i, 3) <= Enddate Then
        r = r + 1
        .Cells(r, 1).Resize(, 5).Value = Application.Index(a, i, Array(1, 4, 5, 6, 7))
        c = a(i, 3) - StartDate
        With .Cells(r, 6 + c)
          .Value = a(i, 2)
          .Interior.Color = IIf(a(i, 2) > 600000, RGB(255, 204, 153), RGB(196, 215, 155))
        End With
      End If
    Next i
  End With
End Sub
 

davidmel20

New Member
Joined
Feb 28, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Exactly what I need, thank you so much peter!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,908
Messages
5,627,578
Members
416,255
Latest member
amethystia

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
Top