categorizing a budget using IF and ISNUMBER?

[LWSheehan]

Hi all! First time posting, thanks in advance.

I think I'm using excel 2010 on Windows 8.

I've got a budget that includes the transaction description, like a bank statement. So it'll have the date in one cell, then amount, then description. I want to write a formula (or many formulas if needed) that'll automatically categorize the transaction into one of 25 categories (eg food or gas or parking). So i want a formula that'll look at the text in the description column, and if it recognizes a key word (eg "Starbucks"), then it'll populate as "food." I'm using the IF function for the logic test, and I'm using the ISNUMBER function to search for the text contained in the cell. But i can only nest 7 levels of this. So i was thinking of putting a bunch of these formulas, with 7 levels at a time in each cell, and then hiding them for cleanliness. If false, the cell would remain blank. So then i figured all I'd need is a formula that looks at all those cells and reports back which one has text in it and what that text was (so if they were all blank except cell A3 which said "food", then that last cell would display "food.") Is there such a formula? Or do you have a more elegant solution?

 

[James006]

Hi,

An easy way-out ... a lot more flexible ... is to build a validation table ... and use VLookup() against this validation table ... to forget all nested IF's ...

HTH

 

[LWSheehan]

Hmmm I'm not sure i follow. I'm not familiar with validation tables but i thought they were for creating drop down menus, right? I don't want that. I want to be able to copy and paste from my bank statement and then have it automatically calculate which category that expense falls under based on a keyword that already exists within the text from the info i pasted. If i have to retype that description field or choose an option from a drop down menu then that defeats the purpose. But then again i don't really understand validation tables so maybe i just don't get what you're suggesting? If you can point me toward a tutorial that shows me how to do what you're suggesting that'd be great. Thanks ever so much!

 

[James006]

Hi,

The validation table is nothing but your own reference list of all possible cases ...

For a tutorial ...https://www.youtube.com/watch?v=uRNlURmep0g

 

[LWSheehan]

I'm sorry but I'm still confused as to how to apply this to my situation. My problem is that the transaction description is not something I can edit (I could, but then that'd defeat my goal). So it isn't an exact phrase or number, it varies depending on the transaction. E.g. "Checkcard 0415 Starbucks 09773" and then on another day it'd be "Checkcard 0287 Starbucks 093728". Both contain the keyword "starbucks," and so I wanted to write a formula that would allow me to identify that part of the cell and then display an answer depending on which keyword exists.

At this point I assume there is no way to do it, so I am going to do my ugly but possibly doable work-around:
I have created three cells that use the IF and ISNUMBER functions. I can nest up to 7 in each cell, so between the 3 cells, I can automatically categorize 21 different types of transactions. For example:

=IF(ISNUMBER(SEARCH("Jamba",C16)),"Food",IF(ISNUMBER(SEARCH("Starbucks",C16)),"Food",IF(ISNUMBER(SEARCH("Ralphs",C16)),"Groceries",IF(ISNUMBER(SEARCH("Green Tree",C16)),"Mortgage",IF(ISNUMBER(SEARCH("Exxon",C16)),"Gas",IF(ISNUMBER(SEARCH("Subway",C16)),"Food",""))))))

But now I've got three columns in which the category can appear. Ideally, I want to hide those columns and add a fourth column that looks and the three cells to the left of it, determines which one has text in it, and then displays that text .

Maybe this will help explain:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Transaction Description[/TD]
[TD]Amount[/TD]
[TD](hidden)[/TD]
[TD](hidden)[/TD]
[TD](hidden)[/TD]
[TD]How do I condense E-G responses in this column?[/TD]
[TD]For example, this is the result I want:[/TD]
[/TR]
[TR]
[TD]7/1/15[/TD]
[TD]GREEN TREE SER DES:MTG PYT ID:XXXXX21266[/TD]
[TD]1471.50[/TD]
[TD][/TD]
[TD]Mortgage[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mortgage[/TD]
[/TR]
[TR]
[TD]7/2/15[/TD]
[TD]Check 582: Babysitting - Lucy[/TD]
[TD]60.00[/TD]
[TD]Babysitting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Babysitting[/TD]
[/TR]
[TR]
[TD]7/2/15[/TD]
[TD]LIBERTY MUTUAL DES:PAYMENT ID:AO226806779740 [/TD]
[TD]88.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]Car Insurance[/TD]
[TD][/TD]
[TD]Car Insurance[/TD]
[/TR]
[TR]
[TD]7/4/15[/TD]
[TD]Starbucks 00004234 ENCINO CA 24164075184[/TD]
[TD]7.25[/TD]
[TD][/TD]
[TD]Food[/TD]
[TD][/TD]
[TD][/TD]
[TD]Food[/TD]
[/TR]
</tbody>[/TABLE]


Is there a way to do this?

 

[LWSheehan]

FYI to the moderators: I'm going to cross post in Chandoo since I am still looking for a response.

 

[James006]

Hi,

How do I condense E-G responses in this column ?

You can use the concatenate() function ...

 

[LWSheehan]

Hi,

You can use the concatenate() function ...

OOoh, yes, that works!!! hooray!

 

[James006]

Glad you could fix your problem ...