Can I get relational data into an Excel Pivot Table

Question

I have a sheet (let's go with wines as an example) that lists every bottle of wine in my cellar, when I bought it, how much I paid etc.

There's a column that describes the wine in comma-separated tags such as "Fruity, White".

I've created a pivot table from that data, with the description as a filter column. However I can't filter it by "White". I have to find every description that contains "White" such as "Dry, White", "White, Crisp" etc.

Being from an RDBMS background, my natural inclination is to put the tags in their own table keyed against the wine row so there's zero-or-more tag rows per wine row.

How, how on earth can I use that to filter the wine rows?

Answer 1

Forgive me if I'm stating the obvious, but the reason you're having problems here is that the description column is not in 1NF, and the Excel pivot interface isn't flexible enough to allow pattern-based searching.

The simplest option will be to normalise the CSV into a series of columns, each of which represents a single attribute - one column for wine colour, one for sweetness, one for country of origin and so on - and apply the filter across multiple columns. However, if (as your comment on the question suggests) wine is a metaphor for your real problem, you may not have the luxury of revisiting the design of the source data.

Another possibility might be to use a macro (or a database query - I'm not clear from your question whether you have implemented the tag system already) to pre-filter the input data on the pivot table's source sheet based on the tag values you want to search for, then re-refresh the pivot table based on that data.

A third possibility is the VBA used in this question, which looks like it will custom-filter the pivot table's visible rows.

Answer 2

=IF(ISERR(FIND("WHITE",UPPER(B5))),0,1)

create an extra column and add a formula. There are 2 tricks to this. One is to search for WHITE in the description column using upper - to beat the fact that excel find is case sensitive. Two is that it returns a value error if the string does not exist - so iserr will allow you to trap that and return in this example 0 if it doesn't or 1 if it does. You could substitute white and blank for 1 and 0.

Answer 3

you could write a script that loops through the data and adds new lines for each comma separated item in the description column. This would allow the pivot table to filter better.

Answer 4

Yes you can do it within Excel and the description fields can remain as "Dry, White" etc as you do not need to split the comma separated values.

Lets say the Table source comprises a text column for Description, a number column for Value and a number column for Year Bought.

Your pivot is setup with the the following

• Fields: Description, Value and Year Bought.

• Column labels: Year Bought

• Row Labels: Description
• Sum of values: Sum of Value

There is a drop down label filter on the row labels - click on this and there should be an option to select Label Filters. Select this and then select Contains. You can enter say "White" which will select all your descriptions that contain white e.g. "Dry, White", "White, Crisp". The filter includes ? to represent a single character and * to represent any series of characters.

There are similar label filters for "begins with" and "ends with" as well as there negation.

I tried this in Excel 2007 and it should also work in 2003. I think in Excel 2003 you could even combine the filters e.g. contains "White" and does not contain "Dry" but in 2007 I could not find a way of doing this.