Have VBA for Unique items in multicolumn range, how to filter multiple rows on previous results?

Question

Intro

I'm trying to enhance a basic planning sheet (see below) with additional sorting.

enter image description here

The first column lists the resources. Each week has 2 columns representing 20 hours per column.

Example readout;

In week 30 Aron works 40h on project A. In week 31 he works 20h on project A and 20h on project B.
Jeff does not work in WK30 and works 40 on project C in WK31

My Goal

Generate a unique list of projects over the weeks
Be able to filter based on project name and get only the rows of the resources working on that project. (No specific need to filter out the other projects in the same row. So if I filter on project "A", I want to only see the rows of Aron and Dave)

What I have

Basically item 1 is covered as follows:

The 2nd column (Projects) is an array-formula generated by a VBA function (taken from here) that returns all unique items in a multi column range. The cell formula looks like this, where the second argument of UniqueItems() determines if we only return the number of unique items (TRUE) or a list of all unique values (FALSE).

=TRANSPOSE(UniqueItems($C$4:$H$6,FALSE))

What is missing

Item 2 of my goal list is missing. If I currently select the filter option for Projects (see screenshot) enter image description here

and filter on Project A, then I only get row 5 and not also row 4. enter image description here

How would I go about filtering this properly?

VBA code is allowed or pointers to which regular formula functions I should use. A complete different solution with the same results is also fine. I thought about pivot tables, but I think it cannot handle empty cells around the range which is common if there's no work for that resource.

The sheet used can be downloaded from here

What about a simple VBA function that could concatenate all the projects from column C onwards into, say, column B, with a delimiter in-between each project, then you could filter column B using a "Contains" criteria? Does that make sense? — John Bustos
I see what you mean and got the concatenation working. However now I always have to choose "column filter (the downward triangle in the column header) -> Text filters -> contains" and then fill in a criteria. I'd prefer to have a few less clicks if you know what I mean. — ixje

spioter spioter · Accepted Answer · 2014-07-24T16:04:46

IMHO, you will be much better off creating a worksheet serving as a normalized database table with one row per the following columns: person, week, project. Lastly, a final column for the number of hours (and optionally, a cost for those hours customized per worker as needed)

Then use a pivot table to build the view you posted (or any other reporting view you need).

This will let you create multiple pivot tables that easily answer questions like: how many hours are planned for project X in total? by a certain date? how many total hours are planned for each worker per time period - who is over/under utilized?
how many total hours are planned for each worker per project? etc.

Keeping the data separate from the reports is safer, and more modular - I wouldn't want a VBA bug to have the potential of corrupting/deleting raw data.

Have VBA for Unique items in multicolumn range, how to filter multiple rows on previous results?

1 Answers