Keeping Track of Garbage in Excel
I’ve worked with “garbage” data many times in Excel. You know what I mean – data that is so messy it takes you hours (or days) to clean it up. This week though, I did a different type of garbage tracking -- an Excel waste collection schedule.
Maybe I went a little overboard, but you’ve probably done that too, right? Anyway, here’s how I spent way too much time on an Excel project, but had fun doing it. Don’t judge! What weird Excel projects have you done?
And speaking of schedules, it's summer now, here in Canada, so I'll be posting every second week. The next article will be posted on July 7th.
Fancy New Carts
In January, our city switched from weekly garbage collection, to bi-weekly. On the alternate weeks, they pick up our recycling. And every week they pick up the food waste, that we store in these large green carts. You need a personal assistant, just to keep track of the collection schedule!
In the photo below, you can see the exciting day when our new carts were delivered. (When you work from home, any unusual activity is exciting.)
Printing the Calendar
There is a PDF file on the city’s website, that has a calendar of the pickup dates. It’s not much use when you print it out though, because the black carts (Garbage) and blue carts (Recycling) print in the same shade of grey.
The green carts are Food waste, but they’re picked up every week, so we don’t have to keep track of that. The brown leaves are the Yard waste – it’s easy to identify them, even without the colour!
Excel to the Rescue
So, since Excel is the answer to every problem, I decided to create a schedule in Excel.
First, based on the PDF file, I listed all the weeks in which our garbage is picked up on a different day. Usually the pickup is on Tuesday, but occasionally a holiday will delay the pickup for a day.
The list is formatted as an Excel table, and named Holidays. The first column is named HolList.
Add the Variables
Next, I created named cells for the regular pickup day, and the type of waste that will be picked up on odd and even weeks.
I started the schedule with this week’s date, so it’s week 1 on my list, and it’s Garbage collection week. Next week is recycling, and it’s week 2 on my list.
In the screen shot below, you can see the yellow cells where the variables are entered. I used the Create Name from Selection feature (on the Formulas tab), to name those cells.
Create a Schedule
On another sheet, I entered the dates for the first two Sundays in 2016. In the column to the left, I numbered the first two rows, as 1 and 2.
Then, I selected those four cells, and dragged down, using the Fill Handle, to create a list of numbers and dates for the rest of the year.
Make a Formatted Table
Then, I entered a few more headings, and formatted the schedule as an Excel Table, named Sched. I created a custom Table Style too, with dark grey dashed lines separating the rows. Why? Because once you start on an Excel project, it’s hard to quit!
Calculate the Collection Day
Finally, after all that setting up, it was time to use some formulas. In cell D4, I entered an IF formula that checks the Holiday List, to see if the “Week Of” date is listed there.
The IF formula checks for the date in the Holiday List, using COUNTIF
- If the test result is True (the date was found), the INDEX/MATCH functions return the pickup day for that date.
- If the test result is False (the date was NOT found), the regular pickup date is returned.
Since this is a formatted table, the formula fills down automatically.
What’s Being Picked Up?
In column E, there’s another formula, and it calculates which type of pickup there is each week – Garbage or Recycling. This is a simple formula, that checks if the ID number is odd or even.
- If the ID number is odd, the formula returns the value in the OddWk named range.
- If the ID number is even, it returns the EvenWk value.
In column F, I manually entered a “Y” for the weeks when Yard waste is picked up.
In column G, I manually entered any special types of collection, such as Batteries, and Christmas Trees.
Doing that was much easier than trying to cook up a formula!
Hide the Past Weeks
My goal was to create a printable schedule, so I wanted an easy way to hide all the past weeks.
In cell H4, I entered a formula that compares the date in the next row to today’s date. I also check for an empty cell, so the last row won’t show TRUE.
Then I can filter that column, to hide all the FALSE rows.
But why stop there? I added a Slicer to the table too, so I can just click a button to hide the FALSE rows. Yes, it’s just a garbage collection schedule, but it deserves nice things too!
Set the Print Area
There’s no need to print column H – it’s just used for filtering the list. So, I selected columns A:G, and set that as the Print Area.
The Final Touch
The waste collection schedule looked good, but since I’d already gone way overboard in setting it up, why not add one final touch?
To make the dates easier to read, I added conditional formatting to separate the months. I selected all the data in the schedule (not the headings), and clicked Conditional Formatting, New Rule
For the Rule Type, I selected “Use a Formula to Determine Which Cells to Format”
In the formula box, I entered this formula, to compare the month in each row with the month in the previous row:
Then I clicked the Format button, and on the Border tab, added a solid black top border.
Completed Excel Waste Collection Schedule
And here is the finished schedule, with past weeks hidden, and lines separating the months.
It prints nicely, and it’s easy to read, so I’ve put a copy on the back door. Now we’ll always know that to put out on waste collection day.
Download the Sample File
If you’d like to see a copy of this masterpiece, and adjust it for your own waste collection schedule, go to the Excel Sample Files page on my website. In the Functions section, look for FN0048 – Waste Collection Schedule.
The zipped file is in xlsx format, and does not contain macros. Maybe the next version will have some!