Auto categorisation of money spending sheet

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"?

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

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

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

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

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

H Jan,

What is your formula in the cell that should be showing KFC?

Regards,

Barry