You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Auto categorisation of money spending sheet

Hi All,


I want to make a money spending sheet in numbers, but I am getting lost in the array formula, so I hope you can help. I tried to search for similar but could not find anything.


I have a sheet which has a tab showing my different spending categories so I can see a detailed monthly overview of my spending. I can already search the table for the categories because I have categorised each line in the bank statement manually.


I now want to automate this, so

  1. I have a table with my bankstatement, and a table with shop names and shop categories
  2. I want to use the bank statement cell to search the shop table (array) for the shop name, which is mixed in there with other text that usually is part of the text.
  3. When the shop name is found, I want to return the shop category name to the cell.

thereby sort of automating the categorisation process.


Can anybody help or if you have an even better idea, that would also be much welcome.



Thanks!


Best...Jan



Posted on Aug 8, 2020 8:08 AM

Reply
Question marked as Top-ranking reply

Posted on Aug 14, 2020 1:56 AM

Hi Jan,


If you want help troubleshooting, the helper needs to be supplied with the information asked for. "The exact same formula that has been copied to all the cells, so they are all identical" does not provide the information needed to determine possible causes of an error.


Looking at the bottom table in your August 10 post, I see 36 in the KFC column. In the table above that I see row 1243 contains three 'words', followed by a large space (or obscured text), followed by KFC. The 36 in the shops index table is what Find would return if the first letter of KFC is the 36th character (including spaces) in the cell being searched. If DKK is also a 'shop', the number in its column will be 10.


With MATCH set to search for 1 and told to 'find smallest' it will find DKK's 10, not KFC's 36, and pass that value to Index.


That's a guess at what is happening. based on what has been shown in your post.


Regards,

Barry


Are any of the first three words "shops"?

Similar questions

15 replies
Question marked as Top-ranking reply

Aug 14, 2020 1:56 AM in response to jklarsen

Hi Jan,


If you want help troubleshooting, the helper needs to be supplied with the information asked for. "The exact same formula that has been copied to all the cells, so they are all identical" does not provide the information needed to determine possible causes of an error.


Looking at the bottom table in your August 10 post, I see 36 in the KFC column. In the table above that I see row 1243 contains three 'words', followed by a large space (or obscured text), followed by KFC. The 36 in the shops index table is what Find would return if the first letter of KFC is the 36th character (including spaces) in the cell being searched. If DKK is also a 'shop', the number in its column will be 10.


With MATCH set to search for 1 and told to 'find smallest' it will find DKK's 10, not KFC's 36, and pass that value to Index.


That's a guess at what is happening. based on what has been shown in your post.


Regards,

Barry


Are any of the first three words "shops"?

Aug 9, 2020 1:22 AM in response to Barry

Hi,



Thanks for a quick reply.


You might be right and I have added a mockup sheet so you can see what I am trying to achieve, hopefully that helps



  1. Bank statement contains long text strings and I only want to extract the shop name
  2. Shopname contains profiled shop names which I have manually categorised
  3. The spending table should then look for the shopname in the bank statement and add the matching categories, so everything is indexed.


I hope this makes better sense and is doable?


Best...Jan


Aug 9, 2020 9:49 AM in response to jklarsen

It's probably possible with quite a bit of work setting up formulas. But wouldn't it be much easier to take the 'Personal Budget' template at File > New in your menu and customize that with your categories?


Each transaction in Transactions table could correspond to an item on your bank statement. For each transaction you simply choose the relevant category for the Pop-Up Menu in the Category column.



The results are then automatically updated on the Budget sheet.


SG


Aug 9, 2020 12:55 PM in response to jklarsen

HI Jan,


Needles and haystacks come to mind, with the added complication that there are several needles, and the search in each case needs to determine which one we've found.


To extract a specific vendor name from each of the Bla bla haystacks, we need to do an individual search for each possible vendor, and stop the search when we've found the one included in 'this stack' or Search for every vendor on the list, mark the fact that 'this one' has (or that 'these ones' have) been found, then pull the one (or 'a' one) that's been found.


I chose the second route, with the option to attach the (extracted) Vendor column to the Bank Statement table or to make it in a separate table (Extracted V)



Vendor index:


A2 and filled down and right to all body cells in the table:


IFERROR(FIND(A$1,Bank Statement::$B2),0)


Each copy of the formula tries to FIND the vendor name at the top of its column in the Description on the same row of the Bank Statement table. If the name is found, FIND returns a number telling its 'first letter' position in the Description string. If it is not found, FIND returns an error, and IFERROR returns the value 0 (zero).


The xxxx value in the 'extra columns ensures there will be an error in the search for these values. If left empty, FIND would return a 1.


Note that FIND is case sensitive— 'amazon' was found (row 2), but Amazon was not (row 7). If the bank statements are consistent in never changing the case of initial letters of the names of specific vendors, you'll need one column for each vendor, if not, you'll need two columns for each vendor where this may occur. A similar caution could be made regarding possible variations in Mcdonalds, Mcdonald's, Macdonalds, etc.


With each 'found' vendor name marked by a value greater than zero in the row matching the row of the Bank statement where it was found.


There are two examples shown. The formulas in each are the same.


Bank Statement::D2:

       Extracted V::A2:  IFERROR(INDEX(Vendor index::$1:$1,1,MATCH(1,Vendor index::2:2,−1)) ,"Other")

Fill down to last row of table.


Maintenance:


Vendor index needs to have the same number of Header rows as Bank Statement, and as many (or more) rows as (than) Bank Statement. The same is true for Extracted V if that table is used.


The Vendor column or Extracted V table will continue to work 'without error' if they contain more rows that the Vendor index table, as errors will be caught by IFERROR and replaced with "Other"


This can serve as a warning that more rows need to be added to the Vendor index table or that the vendor named in Bank statement's Description is not listed in the header row of Vendor index, and needs to be added.


Regards,

Barry

Aug 10, 2020 10:51 AM in response to Barry

Everything has been added and it seems to be working for the most part.


I have discovered some issues where the storename is not found as you can see from this screenshot.

KFC works in row 1241, but not in row 1243?



Even though it has been found in the shop index as you can see here:


APPLE is also found in other cells also not in 1244?


Any trouble shooting tips?


Best...Jan

Aug 8, 2020 11:11 AM in response to jklarsen

Hi Jan,


I think he first thing you should do is extract the shop names and category associated with each shop name from the jumble and place then into a new two column table.


Doing that will produce a lookup table, which can be searched using VLOOKUP or INDEX(MATCH) to extract the category associated with each shop.


"I have a sheet which has a tab showing my different spending categories…"


The only "tab" a Sheet has is the tab showing the name of that Sheet at the top of the document window. Clicking the tab brings its Sheet to the front.

Did you perhaps mean "I have a sheet which has a table showing my different spending categories"?


A screenshot showing the structure of your bank statement table and one showing the structure of your "shop names and categories" table would be useful in determining a solution. Replace the specific names in the statement with 'shop 1', 'shop 2' etc. and change the amounts (I usually decide on a fixed amount—say 1.00 or 9,99 and use that for each line to make manual calculation of the balance amounts (if stated) easy.


Regards,

Barry

Aug 9, 2020 8:58 PM in response to Barry

Hi,



This looks like what I am after.


Just for my understanding. The vendor index, is that the "Extracted V" table or the "vendor" column in the bank statement?

I assume the extracted table right?

So once found, the formula adds the found vendor name to the bank statement vendor cell - right?


I would like to add a column "B" containing a category column next to "Extracted V" , and add a column "E" to the bank statement table containing "Category"

How would the "IFERROR(FIND(A$1,Bank Statement::$B2),0)" formula look if I want to return the vendor name and category to the "Bank statement" table?


Because then I think we should be there.



Best...Jan


Aug 9, 2020 11:20 PM in response to jklarsen

Hi Jan,


"Just for my understanding. The vendor index, is that the "Extracted V" table or the "vendor" column in the bank statement?"


No. Vendor index is the large table that tells where to find the vendor name extracted from the Description in each row.


Extracted V is a table that retrieves the Vendor name for each row, using the non-zero number in that row of Vendor Index.

The Vendor column (D) of the Bank Statement table is an added column which does the same job as the single column of the Extracted V table.

These two do the same job, but only one is necessary. Us whichever better fits your table and workflow.



"Maybe its my typing but the formula " Extracted V::A2:  IFERROR(INDEX(Vendor index::$1:$1,1,MATCH(1,Vendor index::2:2,−1)) ,"Other")" does not seem to be working?"


More likely my lack of clarity.



There are two examples shown. The formulas in each are the same.

The first example in the formula in "Bank Statement::D2:" (and filled down from there)

The second example is the formula in "Extracted V::A2:"  (And filled down from there)


"Bank Statement::D2:" and "Extracted V::A2:" tell the cell in which to enter the formula.


IFERROR(INDEX(Vendor index::$1:$1,1,MATCH(1,Vendor index::2:2,−1)) ,"Other")

is the formula to be entered in whichever of those cells you find appropriate to your document.


Remove the part to the left of IFERROR and all should be OK.


In either location the formula will give you a list of the vendor for each line of the BankStatement table, which you can use with VLOOKUP or INDEX / MATCH to get the main category and subcategories for that vendor from your Shop Categories table.


Regards,

Barry

Aug 10, 2020 12:59 AM in response to jklarsen

Hi Jan,


"How can I get it to also add the category next to the shop name in Bank statement?"


Add two columns, E and F to the right of the Vendor column (D).

Enter these VLOOKUP formulas in E2 and D2 respectively, and fill both down to the end of the columns.


E2: VLOOKUP($D2,Shop Categories::$$A:C,2,Exact match)

F2: VLOOKUP($D2,Shop Categories::$$A:C,3,Exact match)


"Exact match" is a menu choice for the last argument in the VLOOKUP function.

Shop Catecories is the table of that name shown in your post of Agust 9.


Regards,

Barry

Auto categorisation of money spending sheet

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.