Putting mismatched text strings into an Excel pivot table

Let's play Excel!

Right, I've got a moderately huge list of expenses where the client has put who they're paying in one column and how much in other (Amongst other things). The spreadsheet I provide usually analyses the type of expenses it is into categories using a two letter code entered at the time of entering the expense. It's a dead simple IF formula under each expense heading but still, people manage to fuck it up.

In this instance, they've put pretty much eveything they've bought all year in the "Other" column and I want to analyse it out. The best approach would be to do it by the name of the supplier, dropping the Details and Amount column into a pivot table.

This particular genius however has used a different way of writing the supplier each time they've entered an expense it seems, i.e. "Post office", "postage", "POST OFFICE" and so forth, meaning the pivot table is nearly as long as the list it's referencing.

I've built a sort of work around which searches for specific text strings, returns an error or not and then sums all the non error cells. The idea being that everything with the word "Post" in it is probably postage of soem sort.

Column A is the description, B is the amount.

Column C is

=PROPER(A2)

which chops out all the odd capitalisation.

Column F (I like leaving gaps) is

=MID($C2,FIND(F$1,$C2),4)

with F1 being the text string I'm looking for.

Column G is

=ISERROR(F2)

With column H being

=IF(G2=FALSE,$B2,0)

Now this works ok assuming you can think of a common text string but it's a fair bit of faffing about to set up and is fairly unreadable.

I was wondering if there's any way to get a pivot table to filter the data it's reading in a similar way to this and thereby reduce the amount of info in it and save me having to build a page of formulas to extract all the different text strings?

If anyone understands what the fuck I'm on about I'll be impressed, this is a horrible, horrible post.

Oh yeah, I'm using Excel 2003.

Jam it back in, in the dark.

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?

There's nowhere I can't reach.

Sorry man, all I can suggest is to build the list of text strings you want to search for, replace all the descriptions with those standards and pivot off of that. That is, combine your formulae together to get something like

IF(ISERROR(SEARCH(F$1,$C2)),$C2,"POST")

Copy/past-values and iterate for however many accounts you're looking for. That should at least catch the majority and then if you need to manually edit the rest.

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

I forgot you could use SEARCH instead of FIND, that would work better as it's not case-sensitive.

I've attached the page I'm working off, without any names or anything. Essentially I'm looking to group all the similar types of expenses together. Usually I'd do this by dumping it into a pivot table by the name of the supplier and then analysing the totals. Because of the variety of spellings used though, this ends up in a pretty big table still. I mean, it's not the end of the world and will take me no more than an hour to analyse the slow way but it'd be nice to know if, next time I get some shit like this in there's an easy way to pick out text strings from the data the client's inputted that are common to a certain type of supplier. For example a pivot table showing the total of everything with "Post" in the description and "Charges" and "Staff". Something like that.

Also, I like learning how to do new silly things on Excel.

Most amazing jew boots

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.

I was speaking idiomatically.

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

What kind of toxic man-thing is happening now?

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.

I like the SUMIF but I'd be more inclined to replace the 1 as the final result in your formula with the value in column B and then have just a plain sum at the bottom. It's a bit easier to read with the net amounts under each heading and if you format all the cells as Accounting style, the 0's come out as -'s making the numbers stand out almost as well as conditionally formatting in the colours (And I try to avoid colouring in spreadsheets in case I need to send them to someone). That's a much better way to do it than mine though, cheers.

As it happens, in the end I ended up just putting it all in a pivot table, then searching for similar entries and copy/pastaing a common description. I've then analysed the different suppliers into expense headings, aka doing it the slow way.

The spreadsheet they use is one of mine which I think is pretty simple but people still manage to fuck it up. Using Data Validation would stop them fucking it too badly but the thing is designed to be sent to any client with a small business so needs the flexibility to be easily alterable, assuming the person altering it has a fucking clue what they're doing.

Just as a matter of interest, I've attached it to this post. I actually made this about 6 years ago when I last worked here and haven't got round to updating it since I've been back. It's still functional but could work better I guess.

The theory is that the client only ever has to fill in the blue boxes and the rest is automated. They're also supposed to edit the expense headings and codes and add more if they need them but a lot of them don't seem to understand that. The one thing I'd love to have it do would be automatically grab the exchange rate off the internet at the date of the purchase, rather than putting in an arbitrary figure as it does at present.

I think as far as a simple bookkeeping sheet goes it's not too bad though.

FELIPE NO

Hmm, you lost me at the data table, I don't think I've ever used that before. Looks like it could be a handy feature though, I may well investigate further...

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.

Apart from that, that's a pretty neat way to approach the problem, nicely done.

What, you don't want my bikini-clad body?

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.

-----------

Jam it back in, in the dark.

I did set up a data table following the guide in the help page but when I tried to replicate yours, the empty cells below the ones I'd entered all returned the total of the figures in the source data. They gave the right answer when I added a text string to search for though. Like I say, misuse of the SUMPRODUCT command I think.

I did consider using lists and to be honest, if I was tailoring one for a specific client I'd probably do it like that. The problem with that though is whilst it does preclude too much mucking things up by the client, you also lose a lot of flexibility in terms of customisation.

Thing is, we don't ever know exactly what a new client will be spending their money on so I use the generic expense headings and two letter codes with the intention that they'll put their own headings and codes in as suits their expenditure. You and I both know it's dead easy to add more options to a drop-down list but in my experience, people tend to panic when you send them into menus. The alternative I guess is to set the source range for the list as a lot longer and ignore empty cells, getting them to add options in when they need them.

The reason I don't use validation for the payment method is that people can write the cheque number in, rather than just "bank". My setup has it so anything other than "Cash" or "Personal" is recorded as a bank payment. I guess in general, I credit people with more intelligence and Excel ability than they have. Things I would consider very simple are often not so to whoever you're sending the sheet to. I might well incorporate a few new idea based on what you did there.

I especially like the way you use an IF statement to return an empty cell rather than a zero if there's nothing at all in the source cell though, I'll definitely steal that.

Cheers Zerg!

There's nowhere I can't reach.