2
1
I want to review and sort my expenditures in a spreadsheet where I have all my transactions listed. Is there a way to automatically label or tag to categorize the transactions like in a budget program. For instance if the word "taco" is found in column B, then Restaurant shows up in column D. Or if "Lowes" in Column B, then Repair supplies shows up in column D.
Graham C
Posted 2012-11-14T18:09:59.777
Reputation: 21
0
This is perhaps not the most elegant way to do it (and not exactly what you asked for)... but it is fairly quick. and will also allow you to have multiple tags for an item (should you need that)
Basically you can add the tags across in the additional columns to the right. Then use the following formula in C2 (where B contains transaction text.)
=IFERROR(FIND(C$1,UPPER($B2)),0)>0
a quick explanation of the functions
FIND looks for the text of 1 cell (C1 in this case) inside of another cell (B2 in this case) and returns a number where the string starts. However this function will return an error if the text is not found so I am using
IFERROR will check to see if the first argument (in this case our FIND function) returns an error, if no error it will return the contents of the find function. If there is an error it will return the second argument (in this case 0)
UPPER Converts the text in a cell to upper case. FIND is case sensitive so "Beer" is different than "BEER" is different from "BeEr" since we probably want to group them UPPER will force them all to BEER.
These 3 functions combine to now return a number if the text is found and 0 if no tag is found. lastly I add >0 to the end to make excel return TRUE/FALSE instead of a number.
If you want to look for a different word to link to the category you can replace C1 with text. If you want multiple criteria for a single tag you can chain them together using the OR
ie
=OR(IFERROR(FIND("CRITERIA ONE",UPPER($B2)),0)>0,IFERROR(FIND("CRITERIA TWO",UPPER($B2)),0)>0)
Pynner
Posted 2012-11-14T18:09:59.777
Reputation: 371