Okay, so here's the trick...

In order to use a PivotTable, your data source needs to be one big set of "contiguous" rows. (i.e. no blank rows). So that's part of what we use the formulas to fix. By running those down from the row below the heading all the way to the last row of data (10 or 10,000 doesn't matter), we create that table.

In addition, since we need to create the big table anyway, that's where we can get the formulas to create the consistent data we need. The real trick here is not the PivotTable, it's creating the formulas to deal with all the stuff that your co-workers throw into the data to make it inconsistent.

In the case of your example, here's what I would do:

- I1: Full Period
- I2: =IF(A2="",I1,A2)
- J1: Full Date
- J2: =IF(AND(ISNUMBER(B2),B2<>""),B2,J1)
- Copy I2:J2 down to the last row of data

For reference, J2 checks if B2 is both a date and not equal to a blank cell. This will ensure that you only get dates and ignore any other notes in the file. You don't need to move them out, you just need to craft a formula that ignores them when you're building your "real" columns to work off.

Now you can build a PivotTable off it. I set the Pivot up in this shape:

Now, at that point I get a Pivot that has all the "work" listed in it. To get to just the design, you click the filter arrow on the Pivot beside Work, choose Label Filters --> Contains and set it to Designing.

If the filter gets too complicated, then create a new column in your data table and use IF, AND, OR statements to come up with a True or False value, then filter the table by that.

At the end of the day, the magic is in the formula set with your table. As long as you have the original source data, you can keep building your criteria off the table to "fix" or "clean" the data from your co-workers, and mine it very very quickly with the Pivot. And if you need help creating a formula... well... that's what we're here for.

I've attached a copy of the workbook for you too.