• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel/Categorization Macro(formula) based on string of text containing keywords

Status
Not open for further replies.
Nallatheryth

Nallatheryth

New Member
Now I spent a few days searching up and down and need to find a solution.

I saw two threads but both are not what I am looking for exactly and I admit, being not too good in VBA, I cant make heads or tales.

What I have: I have 4300 lines of Bank statements. There are multiple columns but 1 is of importance - Description. This description might contain a lot of things, but usually there is 1 key word that is crucial. Roughly 96% can be automated and 3-4% just written manually every now and then.

What I want:

A VBA Macro that will read the column description, will match a keyword there from a list of many such in Sheet2, column "keywords" and then write in Column Category (sheet1) the assigned Categorization taken from Column Category on Sheet2.

What I have done so far:

the only thing I found to be working for me, and be able to actually reproduce is using a formula:

Code: 
=IF(ISNUMBER(SEARCH("KEYWORD",[Description])),"OUTPUT","")

The above formula was repeated multiple times but this slows and lags everything. Besides being unmanageable.

Its working but I need something better. So -> enter Macros. and here I am lost.

I found that the answer of @JohnBustos is very good here: How to group excel items based on custom rules? but not working for me really.

I found the answer of Tomk Dallimore to be what I need or want: Categorizing bank transactions in Excel

but I cant make heads or tales how to get there??? He is very detailed but I am getting lost on the complexity which mind you is great.

Can you please help me?

I am attaching a very simple example of what I am talking.

http://1drv.ms/1Putpy5


I dont mind doing this with a formula. So far I tested several Array formulas and indexers but somehow I end up having wrong values or no values.



Thank you everyone!


Note#1
I founnd a new formula that I incorporated.
'=IFERROR(LOOKUP(10^10,SEARCH(" "&KeywordTable[In-keyword]&" "," "&H29& " "),KeywordTable[Out-keyword ]),"")

But this is also troubling the CPU as it calculates each time a cell is moved. I imagine it will throw exception if I add 560 new rows or better yet move the table with 1 poisition. Temporary solution but need something more sophisticated.
 
Last edited:
1) y = 4
2) Read 'SOURCE DATA!description' from row y
3) Search previous from 'Keywords!Keyword'-sheet
4) if find then add 'Keywords!Designated Category' to row y
5) y = y+1
6) repeat steps from 2 to 5 until all rows done
 
Thank you so much. your example file was very helpful. I just want to know- if I want to search keywords from two columns- 4 and 5 instead of only 5 in the example file, how can it be done ??
Thanks in advance.
 
vletm said:
Maybe I could do like this...
Click to expand...
Thank you so much bro/sis. this is like so so so helpful. the only thing i am stucked now is that your code is giving two values if certain keywords are present in both the columns. Is there a way if the code search one column and gives a value if found, then it does not check the other column.
Please reply asap.
Again, thanks in advance.
 
This is awesome, could you advice/show how to assign multiple categories? For example if the description contains both Starbucks and Wall Mart.
My goal is to be able to filter by category after categorizing is done and also calculate totals.
Thank you in advance. This is not ASAP :)
 
Status
Not open for further replies.
Back
Top