LWSheehan
New Member
I have a budget spreadsheet and I'm trying to create a way to automatically categorize it by type based on a keyword search of the Transaction Description column. For example, I'll have a transaction that has the date in A3, the $ amount in B3, and the transaction description (e.g. "Checkcard 0415 Starbucks 09773") in C3.
My ultimate goal is to create a formula in D3 that I can tailor so that it searches the descriptions in C3 for certain keywords, and if they exist, then it'll assign a corresponding category. I can do this using the IF and ISNUMBER functions. Basically, I tell it that if the keyword "Starbucks" appears in the description cell, then the response if true should be the category "Food," and the response if false is to leave it blank. However, I can only nest up to seven levels of this. For example:
=IF(ISNUMBER(SEARCH("Starbucks",C16)),"Food",IF(ISNUMBER(SEARCH("Jamba Juice",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",""))))))
So my first question is: is there a better way to do this?
If not, my second question is:
Let's say I use the above formula, and just use multiple cells to cover all of the keywords possibilities (e.g., 3 cells with 7 nested IF functions, allowing me to automatically categorize up to 21 different types of transactions). I now have the category titles that I want appearing in cells D3, E3, and F3. Can I create a formula in G3 that looks at D3:F3 and determines which one isn't blank, and then displays the text that appears in the non-blank cell? In other words, G3 would summarize whatever answer appeared in D-F (there will be only one cell with text in it)?
If yes, I could just hide columns D-F and only show column G, which is a cleaned up, vertical list of the responses in D-F. Then I could use column G to help me SUMIF by category. I hope this makes sense. Right now I'm just typing in the category by hand and I want to avoid that step if possible.
I've attached a sample showing what I've done so far, and then which column I need help with, to get the desired result. Thanks in advance!
My ultimate goal is to create a formula in D3 that I can tailor so that it searches the descriptions in C3 for certain keywords, and if they exist, then it'll assign a corresponding category. I can do this using the IF and ISNUMBER functions. Basically, I tell it that if the keyword "Starbucks" appears in the description cell, then the response if true should be the category "Food," and the response if false is to leave it blank. However, I can only nest up to seven levels of this. For example:
=IF(ISNUMBER(SEARCH("Starbucks",C16)),"Food",IF(ISNUMBER(SEARCH("Jamba Juice",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",""))))))
So my first question is: is there a better way to do this?
If not, my second question is:
Let's say I use the above formula, and just use multiple cells to cover all of the keywords possibilities (e.g., 3 cells with 7 nested IF functions, allowing me to automatically categorize up to 21 different types of transactions). I now have the category titles that I want appearing in cells D3, E3, and F3. Can I create a formula in G3 that looks at D3:F3 and determines which one isn't blank, and then displays the text that appears in the non-blank cell? In other words, G3 would summarize whatever answer appeared in D-F (there will be only one cell with text in it)?
If yes, I could just hide columns D-F and only show column G, which is a cleaned up, vertical list of the responses in D-F. Then I could use column G to help me SUMIF by category. I hope this makes sense. Right now I'm just typing in the category by hand and I want to avoid that step if possible.
I've attached a sample showing what I've done so far, and then which column I need help with, to get the desired result. Thanks in advance!
