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.

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

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.

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

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.

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

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!

I was speaking idiomatically.