2

My boss wants our bank transactions categorized manually. Right now my task is to go through the bank transactions and categorize them into categories based on what each item is. For random expenditures (one time only) it makes sense to look it up and type out what category it is in but for recurring things (same company providing same service every time) it makes sense for it to be automated especially when there are a lot of transactions.

The bank transactions exports into excel by Date, Description, Amount, Type (debit credit etc).

What I want to do is have a excel go through the "Description Column" and convert it into something simple and readable. Example Description column contains "Company xyx 4003 ADP" I want excel to return "payroll" the next cell contains "#3k322 Phil Marqu LLC" and I want it to return "Cafe supplies". I want to do the same for deposits example "Deposit WEB PMTS" should become "online sales"

This way I can then create a table and sort by category ie "cafe supplies" and "online sales" to create a simple monthly report showing money that came in and money out.

My ideal situation involves a seperate reference table that I can keep on adding to that would have 2 columns. Column A would be what substring to match and Column B would be what to return. Example I can put in column A "#3k322" because I Know it will always show up for the cafe supplies vendor and column B would be "cafe supplies" and if in the future we add another cafe suppplies vendor I can just add another row to my reference table.

I've read a lot of things online about index and match and vlookup but I'm not an excel pro and am having difficulty figuring this out. I will learn whatever functions you throw at me. I am very motivated to do this.

Thank you.

2 Answers 2

4

You can create an Index Array where you write the keyword in a column and the corresponding category in the second column, same row:

#3k322     Café supplies
WEB        Online sales

The keyword should always be contained in the description you already have. Create a new empty column near the description for category and write this formula in front of the first description:

=OFFSET($I$2,SUMPRODUCT(--ISNUMBER(FIND($I$2:$I$4,B2,1))*ROW($I$2:$I$4))-2,1)

Where

  • $I$2:$I$4 is the column of keywords starting in I2
  • J2:J4 is the column of corresponding categories
  • B2 is the first description
  • SUMPRODUCT will find the keyword corresponding to the description in B2 and give its row number
  • OFFSET will find the category corresponding to the row found. -2 because I2 is the origin, and 1 for the second column

You can drag the formula down. Keep the $ where I wrote it in the formula and change the reference to correspond to your data.

Update
In order to append the category without changing the formula use the following

=OFFSET($I$2,SUMPRODUCT(--ISNUMBER(FIND(INDIRECT($L$1),B2,1))*ROW(INDIRECT($L$1)))-2,1)

where L1 is a reference of the keywords column, every time you change the categories (append or remove) write in L1 the references: $I$2:$I$4
Keep in mind to write the keyword exactly as it is written in the description (upper case or lower find is case sensitive)
INDIRECT will read L1 and convert it to $I$2:$I$4

enter image description here

8
  • This looks much simpler than the macro suggested in the other answer. Will report back with results. Thank you
    – Pulse
    Feb 10, 2017 at 20:58
  • @yass - Ooo! I love this. I would have gone with an index-match solution but yours is keywords, not the entire string!
    – JSmart523
    Feb 10, 2017 at 21:13
  • It appears to work but I have to change the function everytime I add a new category is there a way to automate that? Also I'm starting to wonder if there is a way to sort the categories even further such as Expenses category with Expenses > Fixed, and Expenses > Variable. So if I can sort based on just looking up Expenses but if I want I can look at only the variable expenses?
    – Pulse
    Feb 10, 2017 at 21:30
  • 1
    This is a great solution! Just note: if there are multiple matches to a text, the offset will overshoot and you get the wrong category. So make sure your categories are unique. Jul 25, 2020 at 9:26
  • 1
    More function explanations: FIND: Matches each of the categories against the text in the cell, returning an array which is mostly #VALUE but one of them will be a number. (If there are multiple matches, the result will overshoot.) --ISNUMBER: turns the array into 0s and 1s. ROW: Finds the row numbers of the categories. SUMPRODUCT: Multiplies the (hopefully) single 1 in the array with the corresponding row number. Subtract 2 to skip the heading and make the offset 0-based. The result is the row offset of the category. Jul 25, 2020 at 9:30
0

I created a macro for a similar project, basically assigning a category to an entry.

Warning: it's inefficient. not a big deal for personal use usually.

Requirements:

two worksheets:

one with transaction names in column one, empty column 2

one with unique transaction names in column one, and their category in column 2

no blank lines

change the for i = 1 - X to equal the number of transactions being processed

change the p line for the number of categories you have

see: https://stackoverflow.com/questions/23025031/excel-compare-two-columns-from-one-sheet-copy-entire-row-on-match-to-new-sheet

Sub AddDepartment()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long, j As Long, p As Long
    Dim isMatch As Boolean

Set ws1 = ActiveWorkbook.Sheets("Sheet1") 'transactions sheet
Set ws2 = ActiveWorkbook.Sheets("Sheet2") 'categorized sheet

'Initial position of first element in sheet2
p = 1

For i = 1 To 100 'last row of transaction sheet necessary
    isMatch = False
    For j = 1 To 100 'last row of category sheet necessary
        If ws1.Cells(i, 1).Value = ws2.Cells(j, 1).Value Then 'if transactions.transactions = category.transactions
            ws2.Cells(j, 2).Copy ws1.Cells(p, 2) 'write category.category to transactions.category
            isMatch = True
            p = p + 1
        End If
    Next j
    If isMatch = False Then
        ws1.Cells(p, 2) = "OTHER" 'set anything that does not match to "other" category
        p = p + 1
    End If
Next i
End Sub
2
  • Thank you for taking the time to respond. How would I implement this into my situation? I think I need a little bit more guidance as I've only used a macro once before and it was much much simpler.
    – Pulse
    Feb 10, 2017 at 19:12
  • create a new module in the visual basic window, and paste the code. Make sure your transaction sheet is named "Sheet1" and your categories sheet is named "Sheet2" and run the macro. Try it in a new test workbook
    – Cpt.Whale
    Feb 10, 2017 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.