How do I do this thing in Excel?
October 25, 2018 10:22 AM Subscribe
Actually, I'm using Libre Office Calc, but as far as I can tell it should work the same in both, so if you can explain how to do this in Excel I'm pretty sure I can make it work in Calc.
I have a personal budgeting spreadsheet that I dump all my transactions from my bank account and credit cards to monthly. I end up with three columns that really matter.
A- Description
B - Amount
C - Category
The category column is a picklist that is populated from a column of category names. I also subtotal the categories using SUMIF and transfer those subtotals to another sheet where I track budget vs. actual on a monthly basis.
What I can't figure how to do is automate the picklist selections. Many of the transactions could be auto categorized based on keywords in the description column. For example, Kroger in the description should always be groceries, WaWa should be dining out (wife's daily Diet Coke habit), FasMart is gas, Geico is auto insurance, etc.
It seems like I should be able to do this using an array with a column of keywords and a related column of category names, but I can't get it to work. What is the magic code I need in the category column to get it to auto populate based on keywords in column A?
I have a personal budgeting spreadsheet that I dump all my transactions from my bank account and credit cards to monthly. I end up with three columns that really matter.
A- Description
B - Amount
C - Category
The category column is a picklist that is populated from a column of category names. I also subtotal the categories using SUMIF and transfer those subtotals to another sheet where I track budget vs. actual on a monthly basis.
What I can't figure how to do is automate the picklist selections. Many of the transactions could be auto categorized based on keywords in the description column. For example, Kroger in the description should always be groceries, WaWa should be dining out (wife's daily Diet Coke habit), FasMart is gas, Geico is auto insurance, etc.
It seems like I should be able to do this using an array with a column of keywords and a related column of category names, but I can't get it to work. What is the magic code I need in the category column to get it to auto populate based on keywords in column A?
I would probably write a macro to do this in Excel. It looks like Libre Office Calc does allow macros. There is a bit of a learning curve but I think filling in a category in column C based on column A is a pretty simple one. It would be easiest to have a hidden sheet at the back with the table correlating descriptions to recommended categories, then when you add a new line it looks up the corresponding recommendation.
posted by muddgirl at 11:00 AM on October 25, 2018
posted by muddgirl at 11:00 AM on October 25, 2018
In Excel, at least, index match is preferred to vlookup. It has been my personal experience that files with a lot of vlookups tend to cause excel to reenact Frozen.
If vlookup/ index match failed to find a reference value, would you want to enter it in manually? If so, you might want to add additional helper columns to bbqturtle's column D. In this case, column E is the manually labeled column, and column F is the new final label column. Column F's formula should be the open office equivalent of the following: if the value of the cell in column D is valid, then the value of column F = value of column D, otherwise value of column F should be column E. (You might need another layer of nested if statements to address the situation where column D yields a valid value, yet does not match the value of column E. )
posted by oceano at 12:32 PM on October 25, 2018
If vlookup/ index match failed to find a reference value, would you want to enter it in manually? If so, you might want to add additional helper columns to bbqturtle's column D. In this case, column E is the manually labeled column, and column F is the new final label column. Column F's formula should be the open office equivalent of the following: if the value of the cell in column D is valid, then the value of column F = value of column D, otherwise value of column F should be column E. (You might need another layer of nested if statements to address the situation where column D yields a valid value, yet does not match the value of column E. )
posted by oceano at 12:32 PM on October 25, 2018
Not quite what you asked, but this is really easy to do in Mint. You can then export all the transactions (bottom right of the transactions view) to get a csv file that you can open in libre calc.
posted by matildatakesovertheworld at 9:25 PM on October 25, 2018
posted by matildatakesovertheworld at 9:25 PM on October 25, 2018
This thread is closed to new comments.
=vlookup([reference to description], [range of columns and categories], 2, FALSE).
If you wanted, you could build this into your column C: =if(iferror(D1),"",D1) would return the value of D if it's not an error. Or, without column D alltogether, you could do =if(iferror(vlookup([reference to description], [range of columns and categories], 2, FALSE),"",vlookup([reference to description], [range of columns and categories], 2, FALSE)).
You would need your range of descriptions and categories somewhere else in two columns.
Now - that's if you have an exact match / repeating description. I know in my statements the descriptions can vary.
A few handy tools for fixing that. You can use the =iferror(search(A1,*"Krog"*)) function to search for a string like kroger. Or, because that can be annoying, you could also just take the left 7 or 8 characters from a cell using the =left(A1,6) and use that for your lookup chart, assuming the first 6 characters are unique enough to not overlap, but consistent enough to identify repeat places.
Hope this helps!
posted by bbqturtle at 10:35 AM on October 25, 2018 [1 favorite]