Categorizing budgets

humility36

New Member
Joined
Dec 16, 2019
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Good Morning Team,

I'm new here and I've been stuck on this issue all weekend. I've tried to figure this out on my own, but I just can't get the logic down. I'm really hoping someone can help me out here.

I've been tasked with categorizing an expense report which is nothing more than a .csv transaction dump from a bank. I've converted the csv to excel and created a table of possible categories. If the transaction description has a specific keyword "like fuel, gas, speedway" than I want the category to say "gas". I've tried vlookup and index and match and nothing is working right. Please see my example below.

ABCDEFG
1Datedescription (1. search this column)withdrawaldepositcategory (4. write the category that matched a keyword)Lookup keyword (2. find a matching keyword)lookup category (3. find matching keyword and use this category in the category column)
211/23speedway gas pos purchase seq# 849315gasgas
311/24subway restaurant10.99fuelgas
411/24aep electric85speedwaygas
511/25kroger fuel25aepelectric utility
611/26paypal payment60subwayfood
7paypalmoney in - see comments
8mortgagemortgage
9starbucksfood
10


I don't know how I would write this logic. I tried doing an array search, but I keep getting a reference error or a #N/A error. My logic is to search the description column for a keyword in the look up table and if there is a match then write the associated category in the category field. Now, I did have a lot of success with using nested if statements, but I have more than 64 nested if statements and I couldn't add any more key words. So now I'm limited to just 64 keyword, not to mention my formula looks out of control and messy.

Sample of my nested if statement. (I want to move away from this because it does not seem like the most effcient and I'm limited to only 64.)

=IF(ISNUMBER(SEARCH("*culligan*",E:E)),"culligan",IF(ISNUMBER(SEARCH("*spotify*",E:E)),"spotify",IF(ISNUMBER(SEARCH("*aqua*",E:E))," water bill",IF(ISNUMBER(SEARCH("*walmart*",E:E)),"grocery",IF(ISNUMBER(SEARCH("*My company*",E:E)),"paycheck",IF(ISNUMBER(SEARCH("costco gas",E:E)),"gas",IF(ISNUMBER(SEARCH("speedway",E:E)),"gas",IF(ISNUMBER(SEARCH("fuel",E:E)),"gas",IF(ISNUMBER(SEARCH("columbia gas",E:E)),"gas utility",IF(ISNUMBER(SEARCH("meijer",E:E)),"grocery","other"))))))))))

Any help would be appreciated I have to do some end of the year reporting and this was assigned to me last minute all because I thought I would be nice and I was able to write 3 basic formulas for my manager who was struggling with some manual data entry in his spreadsheet. (*facepalm*)

Thanks everyone for your help in the future,
Humility36
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi & welcome to MrExcel.
How about
=INDEX($G$2:$G$9,AGGREGATE(15,6,(ROW($G$2:$G$9)-ROW($G$2)+1)/(ISNUMBER(SEARCH($F$2:$F$9,B2))),1))
 
Upvote 0
@Fluff,

Thank you so much for your prompt reply! Your solution is working great! Is it possible to enhance this just a little bit? What I'm finding out is if there is no keyword available, the cell calculates to "0". Instead of the cell being zero (0), can I modify the formula to say the text "other". I'm finding that there are some transactions that do not fit in any of the categories I've been given and so I need to dump them into an "other" category to follow up on.

My end goal here is to get the data formatted and configured to then move it to some pivot tables and reports.
Thanks again for your swift response! It's really helped me get my day up and running on this.

Respectfully,
Humility36
 
Upvote 0
If there is no keyword match you should get a #NUM! error. If you are getting a 0 that suggests a match was found, but col G did not have a value.

You can deal with the no match like
=IFERROR(INDEX($G$2:$G$9,AGGREGATE(15,6,(ROW($G$2:$G$9)-ROW($G$2)+1)/(ISNUMBER(SEARCH($F$2:$F$9,B2))),1)),"other")
 
Upvote 1
Solution

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top