The Problem: I want to auto categorize my bank transactions by the transaction description. But the transaction description is often concatenated with identifier numbers, additional text, lower and upper casing and many more weird wording formats! For example:

bank transaction description in weird format

What are my options to match similar text by keywords so that I can auto categorise the transactions?

  1. Vlookup-
  2. Match-
  3. index, search, etc..
  4. Microsoft Fuzzy Lookup Addin

The Solution: I choose Microsoft Fuzzy Lookup Addin. The reasons for doing so are (i) the first 3 options did not give me good results as it cannot deal with complicated text formats like concatenated words. (ii) The Microsoft Fuzzy Lookup Addin was developed by a team of research team thus would be highly evident and specialized to text analytics techniques.


Steps to install Microsoft Fuzzy Lookup Addin

Step 1. Download Microsoft Fuzzy Lookup Addin from this link:

Step 2. Close all currently open Microsoft Excels. Then start to install by following the onscreen instructions.

Step 3. Then open up Microsoft Excel again. Then start to install by following the onscreen instructions.

Steps to prepare the excel file for matching similar text and categorizing the text

Step 1. Create a new “Keywords” excel sheet with 2 columns Description and Category

Step 2. Populate the Description and Category

Step 3. Make it into a Table by going to “Insert > Table >  Select the exact range for Description and Category”

Step 4. Rename the Table by going to “Table Tools > Design > Rename the “Table Name:”  as “Keywords”

Step 5. Create a new “Transactions” excel sheet with the transaction data you downloaded from your bank

Step 6. Repeat Step 3 and 4 to make it into a Table and rename it as Transactions. You should see something like the following:

fuzzy match excel sheet similar text

Steps to auto categorize the transactions by matching similar text and wording from the Transactions excel sheet with Keywords excel sheet

Step 1. On the Transactions excel sheet, select the left most empty cell where the Addin will auto input the category text

Step 2. Click on Fuzzy Lookup tab on the excel ribbon on the top. Then click on Fuzzy look up button.

Step 3. See this image for configuring the matching. Left Table = Transactions. Right Table = Keywords.

excel sheets match similar words keywords with weird format

Step 4. For similarity threshold, you may want to adjust left side to less than 0.4

Step 5. Click “Go” Button

Step 6. You should see the following results. Repeat Step 3 to 5 until you are satisfied with the matching results.

sheets match not exactly same description text string results