I'm trying to group expenses in a bank statement using Google Sheets. I would like to know how much was spent on Gas, Groceries, etc.

Data

Bank Statement

The bank expenses are imported in the following format:

+----------+-----------------------------------------------------------+---------+------+

|   Date   |                        Description                        | Amount  | Type |

+----------+-----------------------------------------------------------+---------+------+

| 01/01/18 | CARD INT USPS INT CITY STATE INT CARD INT                 | -AMOUNT |      |

| 01/01/18 | CREDIT INT CURRENCY CITY STATE                            | +AMOUNT |      |

| 01/01/18 | CARD INT GROCERY STORE #0001 CITY STATE INT CARD INT      | -AMOUNT |      |

| 01/01/18 | CARD INT DIFF GROCERY STORE #0002 CITY STATE INT CARD INT | -AMOUNT |      |

| 01/01/18 | CARD INT GAS STATION #0001 CITY STATE INT CARD INT        | -AMOUNT |      |

| 01/01/18 | CARD INT DIFF GAS STATION #0005 CITY STATE INT CARD INT   | -AMOUNT |      |

| 01/01/18 | CREDIT INT CURRENCY CITY STATE                            | +AMOUNT |      |

+----------+-----------------------------------------------------------+---------+------+

Type Table

I made a type table that has the following data:

+-----------+--------------------+

|   Type    |      Keyword       |

+-----------+--------------------+

| Gas       | GAS STATION        |

| Gas       | DIFF GAS STATION   |

| Groceries | GROCERY STORE      |

| Groceries | DIFF GROCERY STORE |

+-----------+--------------------+

Algorithm

What I'm thinking of doing is:

• For every Keyword in the Type table
  
  
  
  
  • If the Description from the Bank Statement Table contains it
    
    
    
    
    • Set the Type column of the Bank Statement Table to the Type column of the Type table
    
    
    
    
  
  
  
  

Desired Result

Such that the Bank Statement Table would be:

+----------+-----------------------------------------------------------+---------+-----------+

|   Date   |                        Description                        | Amount  |   Type    |

+----------+-----------------------------------------------------------+---------+-----------+

| 01/01/18 | CARD INT USPS INT CITY STATE INT CARD INT                 | -AMOUNT | FALSE     |

| 01/01/18 | CREDIT INT CURRENCY CITY STATE                            | +AMOUNT | FALSE     |

| 01/01/18 | CARD INT GROCERY STORE #0001 CITY STATE INT CARD INT      | -AMOUNT | Groceries |

| 01/01/18 | CARD INT DIFF GROCERY STORE #0002 CITY STATE INT CARD INT | -AMOUNT | Groceries |

| 01/01/18 | CARD INT GAS STATION #0001 CITY STATE INT CARD INT        | -AMOUNT | Gas       |

| 01/01/18 | CARD INT DIFF GAS STATION #0005 CITY STATE INT CARD INT   | -AMOUNT | Gas       |

| 01/01/18 | CREDIT INT CURRENCY CITY STATE                            | +AMOUNT | FALSE     |

+----------+-----------------------------------------------------------+---------+-----------+

From here, the Type Table could be expanded to account for new line items in order to categorize them. (i.e. rows with FALSE in the type need a match from the Type table), and could be grouped and summed based on the type.

Attempted Solution

In the Type column of the Bank Statement table the closest I've gotten to success is:

=INDEX(Type!A:A,MATCH(TRUE,ISNUMBER(SEARCH(Type!B:B,B10))),0)

This should:

1. SEARCH(Type!B:B,B10) - Search the Type table across all Keywords and match to the Description (not sure this can take a collection as the first argument)


2. MATCH(TRUE,ISNUMBER((result from 1.))) - Cast the results to either TRUE or FALSE for each row


3. =INDEX(Type!A:A,(result from 2.),0) - Get the Type from the Types table

Grateful for any help you can provide