☰

How do I use Excel to categorize bank transactions into easily understandable categories to see where my money is going?

tag-icon • tag-icon microsoft-excel worksheet-function accounting
How do I use Excel to categorize bank transactions into easily understandable categories to see where my money is going?

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.

答案1

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

答案2

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

相关内容