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. 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!

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.)

garbagecarts01

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!

wastecollection01

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.

wastecollection04

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.

namecreatefromselection02

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.

wastecollection05

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!

wastecollection06

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.

=IF(COUNTIF(HolList,[@[Week Of]]),
        INDEX(Holidays[PU Day],MATCH(C4,HolList,0)),
        RegDay)

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(ISODD($B4),OddWk,EvenWk)

  • 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.

Manual Entries

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!

wastecollection07

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.

=AND(C5<>””,C5<TODAY())

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!

wastecollection08

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.

wastecollection02

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:

=Month($C4)<>Month($C3)

Then I clicked the Format button, and on the Border tab, added a solid black top border.

wastecollection09

The Finished 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.

wastecollection10

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!

_________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.