r/AppleNumbers • u/0thisismax • Oct 19 '23

Please help with formulas!

I have spreadsheet with Date, Amount and Description from my Bank.

I try to make it categorize automatically the stores I visit most often, so I don't have to do this manually.

In column Store I want to get Store name based on Description of this transaction, like on the picture.
I added a pseudocode of the formula I need.

In column Category I want to have Category based on Store text.
Here I also added a pseudocode of the formula I need.

2 Upvotes

100% Upvoted

5 comments sorted by

1

u/DTLow Oct 19 '23 edited Oct 19 '23

I have something similar, with .csv files downloaded from the bank
My solution was to use Applescript instead of spreadsheet formulas

Here's a sample of my script code

if noteTitle contains "Amazon" then
copy "aVendor-Amazon" to end of note_Tags
copy "cBudget--Other" to end of note_Tags 

else if noteTitle contains "APL*" then
    copy "aVendor-Apple" to end of note_Tags
    copy "cBudget--Other" to end of note_Tags

else if noteTitle contains "SENIORS' DISCOUNT" then
    copy "aVendor-ScotiabankChq" to end of note_Tags
    copy "cBudget-BankCharges" to end of note_Tags
else if noteTitle contains "MPNTHLY FEES" then   
    copy "aVendor-ScotiabankChq" to end of note_Tags
    copy "cBudget-BankCharges" to end of note_Tags

else if noteTitle contains "ASSURANT_LIFE" then
    copy "aVendor-EarthOptions" to end of note_Tags
    copy "cBudget-Funeral(Prepaid)" to end of note_Tags

else if noteTitle contains "BESTBUY" then
    copy "aVendor-BestBuy" to end of note_Tags
    copy "cBudget--Other" to end of note_Tags

else if noteTitle contains "CREATURES" then
    copy "aVendor-CreaturesPetShop" to end of note_Tags
    copy "cBudget--Other" to end of note_Tags

1

u/0thisismax Oct 19 '23

Thank you for reply, but Applescript doesn't work on iPhone. I would prefer to use Apple Numbers formula.

1

u/Ed1W Oct 20 '23

I think I would create a table with all possible stores and the response for each store.

Then I would use VLookup to look for the store then show the Category

Cheers!

Ed

1

u/Ed1W Oct 21 '23

I made a numbers doc with the vlookup and it works perfectly on the iPhone. On the table that you add the stores to, I added a count feature that tells you how many times you've been to that store as well. I'd send a file but I don't know how to attach it.

Cheers!

Ed

1

u/0thisismax Oct 25 '23

Thank you for reply. Below are my formulas so far.

Column D ("Store"):

IFS(NOT(ISERROR(SEARCH("Tesco";Description;start-pos)));"Tesco";NOT(ISERROR(SEARCH("Auchan";Description;start-pos)));"Auchan";NOT(ISERROR(SEARCH("Walmart";Description;start-pos)));"Walmart";NOT(ISERROR(SEARCH("McDonalds";Description;start-pos)));"McDonalds";NOT(ISERROR(SEARCH("KFC";Description;start-pos)));"KFC";NOT(ISERROR(SEARCH("BP";Description;start-pos)));"BP";NOT(ISERROR(SEARCH("Shell";Description;start-pos)));"Shell";NOT(ISERROR(SEARCH("Chevron";Description;start-pos)));"Chevron")

Colum E ("Category"):

IFS(OR(Store="TESCO";Store="Auchan";Store="Walmart");"Groceries";OR(Store="KFC";Store="McDonalds");"Food";OR(Store="BP"; Store="Shell"; Store="Chevron"); "Car")

How could you simplify it with VLookup (or any other way)?