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.