2

I have a 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 the Store name based on the Description of this transaction, as in the table below.

Transactions
Date Amount Description Store Category
2023-10-19 100 KRA TESCO 01 Tesco Groceries
2023-10-18 50 WR Auchan 05 Auchan Groceries
2023-10-17 70 McDonalds 65 McDonalds Food
2023-10-16 30 KFC PL KFC Food
2023-10-15 50 WR TESCO 15 Tesco Groceries
2023-10-14 24 BP 01 BP Car
2023-10-13 56 Shell 45 Shell Car
2023-10-13 56 Shell 45 Shell Car
2023-10-13 70 US Walmart 32 Walmart Groceries
2023-10-11 95 US Walmart 15 Walmart Groceries
2023-10-09 42 Chevron 23 Chevron Car

Below are my current formulas:

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 can I simplify these formulas (mostly the first formula)?

The first formula is basically the repetition of NOT(ISERROR(SEARCH(“Store-Name”;Description;start-pos))) (which returns TRUE if "Store-Name" is found or FALSE if "Store-Name" is not found) for each Store. How could I simplify it?

1 Answer 1

1

I think one way of simplifying them would be to use a second table as a small database combined with the REGEX.EXTRACT function for the first one and the XLOOKUP function for the second one as shown in the screenshot below:

enter image description here

In the screenshot, the formula starting with IFERROR shows the one in D12 and the formula starting with XLOOKUP shows the one in E12. Also find below the same formulas in text:

Cell Formula
D12 =IFERROR(REGEX.EXTRACT(UPPER(Description '2023-10-09');"\b"&TEXTJOIN("|";1;Stores::Store)&"\b";1);"-")
E12 =XLOOKUP(Store '2023-10-09';Stores::Store;Stores::Category;"-";0)

P.S. If you are not familiar with the regex functions in Numbers, one of the places to start learning about them would be this excellent video introducing them by Gary Rosenzweig of macmost.com.

1
  • This works, thank you! And thanks for providing the video too. Oct 26, 2023 at 8:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .