0

My bank statement has a description column which tells me where I spent the money or where I got it from. Based on this information I fill a column manually to be able to catagroize these expenses - all the transactions of client A, all the transactions of amazon. this is based on me reading the description and finding a keyword. Can I automate this process. I tried with a extremely long formula - if(search("amazon",a1),"Amazon"), elseif(search ........ It's too complex and prone to errors. There would be around 20-30 catagories.

  • Look up LOOKUP(). – Scott Aug 4 '15 at 6:08
  • @scott: "amazon" is probably not the whole content of the cell, so I don't think LOOKUP() would work here. @Shitij: I'm not aware of any built in function, you probably would need VBA if you really want to get rid of the long formula. – Máté Juhász Aug 4 '15 at 8:37
  • What is the formula? We can't troubleshoot it unless you show us.. Also - try using mint.com – Raystafarian Aug 4 '15 at 9:19
  • @MátéJuhász: Your guess is as good as mine until the OP provides us with more information.  … (Cont’d) – Scott Aug 4 '15 at 12:11
  • (Cont’d) …  OP: I deliberately wrote a very short comment six hours ago because of the vagueness of the information you posted in your question.  Please edit your question to show us some data.  It doesn’t need to be a year’s worth of data, or even a month’s worth, or even 20 categories — or even any real data — just show a few rows of data that are representative of your real data.  See here and here for examples of how to post spreadsheet data in Super User without using screenshots.  … (Cont’d) – Scott Aug 4 '15 at 12:12
0

The approach of your formula can get you close, but as you say, long formulas are hard to troubleshoot, and with SEARCH, you have to deal with error values when the target isn't found. If you break the problem down into individual keywords, there is an easy solution. Here is a version and I'll step through what I did.

screenshot

Column A contains the descriptions. Column B is the keyword found for the transaction. Columns starting with C are for your keyword targets, one target as a column heading in each column. As a one-time task, fill in your keywords. You can add a keyword at any time and just copy the formulas into the new column from an adjacent column.

The formula in C2 can be copied across and down. Each cell in these keyword columns looks for that column's keyword in that row's description. The formula I used in C2 is:

=IF(ISERROR(SEARCH(C$1,$A2)),"",C$1)

This searches for the target keyword and displays it if found. Otherwise, it displays a blank. If your target word is not always the keyword, you can replace the last parameter in the formula with the keyword. However, you will then not be able to simply copy the formula into any cell without modification.

The formula in B2 is:

=C2 & D2 & E2 &...

(Spaces added for readability.) If you think you might add more keywords, you can concatenate some extra columns into the formula initially. This just concatenates the results of the keyword searches for the row. There should be only one matching keyword, so stringing that together with a bunch of blanks for the other keywords gives you the matching keyword for the transaction. If none of the keywords match, the cell will be blank. If you add more keywords later, make sure B2 contains the new column references and then copy that down the column.

Aesthetics

If you don't want to see the keyword helper columns, you can hide them or stick them in an area that is out of view.

If you want to do everything in a single formula that doesn't rely on helper columns, just start with this approach to build the formula. This allows you to create a single, short formula, and copy it to other columns and rows to verify that everything is working. At that point, you can turn B2 into a comprehensive formula by substitution.

In B2, replace the C2 reference with the formula in C2 (copy and paste the formula in C2, except the equal sign, to replace the C2 cell reference), and repeat for each cell reference. Now you have a formula that refers to just a list of keywords (which you could move to a vertical list if you want).

If you don't want even the keyword list, replace the keyword cell references with the actual keywords. You will then have a completely self-contained formula. When B2 is to your liking, copy it down the column.

Not the answer you're looking for? Browse other questions tagged or ask your own question.