Putting mismatched text strings into an Excel pivot table

At the risk of asking you to violate your client's secrecy, could you post an obfuscated version of the spreadsheet, and say what are the results you wanted?

Jam it back in, in the dark.

You need to give that client a good thrashing. Also, it wouldn't hurt to limit his options to a list already prepared by you, using Data Validation rules.

Anyway, I'd just skip out on the PivotTable and do it like this (see attachment).

The highlighted green columns should be copy/paste-able, and you can add the keyword at the first row. The ErrChk column is just to see if any row has been counted twice (say, an entry that has both 'post' and 'petrol'.

It's not the elegant solution that I think you're looking for, but what the heck. The additional function I used was "SumIf". By the way it's set up however, "Sumproduct" would work just as well.

There's nowhere I can't reach.

Oh, and after a bit more fooling around, I remembered that the magic of Data Tables can remove the need for multiple columns entirely.

Take a look at this. All you do is type in your keyword at the highlighted cell in column F, and the subtotal will automatically appear.

Do note that the error-checking no longer exists, so it's possible for an entry that has, say, "post office parcel", to be caught by both "post" and "parcel".

This thing is sticky, and I don't like it. I don't appreciate it.

Originally Posted by Shin

Hang on, let me get my head round this.

=IF(F$1="","",IF(ISERR(SEARCH(F$1,$A448))=TRUE,0,1))

If the top cell is empty, return an empty cell, otherwise if the text at the top isn't in the box give a zero, if it is, give a 1.

More or less. It goes like this:

1. If F1 is empty, then don't return anything.
2. See if the text at the top matches the one in the box. Does it evaluate into an error (#VALUE in your case)?
   • If yes, then return zero.
   • If no (there's a match), then return 1.

You can, of course, choose to have it return the value, and do a simple sum. Many ways to do things.

Originally Posted by Shin

Edit: How come when I tried to replicate that, all the cells where I hadn't put anything in the data table had the total of the amount range in rather than the zero you've got? I think I might be using SUMPRODUCT slightly wrong.

The data table is an array formula, you'll have to do it all at once instead of one-by-one.

You define a base scenario (which I shaded with black). In this case, I equated D1 (tax) as the independent variable and the sumproduct of d1:d454 with b1:b454 (or the subtotal) as the dependent variable.

You basically use data tables to do what-if analysis. As such, you will find them in that section in Excel 2007. I don't recall off-hand where you can find them in Excel 2003, but just have a look around.

What you basically need to do is to select a range where you want the data table to work, making sure the base scenario is at the top. Upon insertion of the data table, it asks you where the input cell is. Since we input at cell D1, to change "tax" to whatever, you define that as the column input cell. Then, that should take care of things.

If you need more explanation, give a holler through PM or any of the IMs listed on my profile.

-----------

It may be possible to set up the exchange rates too. If you need daily quotes, you can use FXHistory to construct a separate worksheet with daily rates, and use VLOOKUP to get the rate for that particular day.

There are also ways to grab the latest rates using the web query tool, but it's not something I've worked with.

-----------

I'd lock down the spreadsheet like so. Note that if I REALLY want to make it foolproof, I'd also protect the whole thing and only make specific cells available for entry

This one makes extensive use of List Validation rules. You can see the list in a separate page.

-----------

I am a dolphin, do you want me on your body?