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:
What are my options to match similar text by keywords so that I can auto categorise the transactions?
- Vlookup- https://www.excel-university.com/perform-approximate-match-and-fuzzy-lookup-in-excel/
- Match- https://exceljet.net/excel-functions/excel-match-function
- index, search, etc..
- 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: https://www.microsoft.com/en-us/download/details.aspx?id=15011
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:
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.
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.
