Hi Gary,
I have a Home Budget spreadsheet in Apple Numbers (version 6.2.1 (6529)) of purchases I’ve downloaded from a .csv file. It lists all my credit card transactions – each row includes the name of the Retailer, Date, and Amount of each purchase in separate columns. I’m trying to add a column to my spreadsheet that uses a formula to categorize the “Type” of purchase, based on the name of the retailer.
For example: If the Retailer field contains “Farm Boy” (a grocery store) then I want “Food” to appear in the “Category” column. Note: There are approximately 6-8 different store names in the Retail field that should result in “Food” being generated in the “Category” column. There are also other categories with similar dynamics, eg. Retailers for gas stations or parking lots that I would want to generate “Auto” in the Category column for those transactions.
Is there a way to do this Numbers?
—–
Grant
The easiest way, by far, is to add the category yourself. So create a column and type short category names like "Food" and "Fuel" and such. You could build a complex set of formulas to do that for you based on the name, but it would take a lot of effort and probably wouldn't be worth the time. Plus you'd have to update it all the time when new names appear and old ones charge, which would happen all the time. One month it is "Bob's Gas Station" and the next the charges from the same place are "Conoco 64392" or something.
There's really no way around it. You've got to manually set the category. Autocomplete will help a lot so you rarely have to type the name out. You can also use copy and paste to do this quickly. I know this from experience as I do this myself.
Also, consider that even if you had a complex set of formulas, it would get them wrong. "Amazon?" That could be food, office equipment, gifts, etc. "Safeway?" That could be gas or food in my case.
Setup another table. Call it "Monthly Bills" for example.
Column A: Who
Columns B - M: Jan to Dec
Column N: Total B thru M
Column O: Avg/month
In Column A list the various categories i.e. Fuel, Food, Medical, Automotive, Electricity, Taxes, etc.
Update the check register, go to the Monthly Bills table and add/subtract the amount to the month for the specific category. My wife and I buy fuel from the same vendor. I have two separate FUEL categories, wife and mine. Easy!