multiple search/find criteria 1

[ceecld]

Hi,

Im trying to catagorise entities in a list into smaller predetermined groups, e.g. take a bank statement and you want to group all entries related to food shopping, drinking, car etc. etc.

Col A Col B Col C
[description] [value] [group]

i was trying to use an array formula with search/find, e.g. {=isnumber(search("groups",A1:A10))} however it only seems to use the first entry in the "groups" list. Is there a way to seach based on multiple criteria?

 

[SkipVought]

Hi,

Have you used the PivotTable Wizard?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceecld]

ultimately ill use a pivot table once i have the simplified groups, but because the descriptions can vary for the same type of entry, i.e. include time stamps, account numbers etc. it's not possible to use the pivot table yet.

 

[SkipVought]

it's not possible to use the pivot table yet"

How is that?

" but because the descriptions can vary for the same type of entry"

That sounds to me like a DATA problem (YOUR problem) that no computer can fix! That is procedural, solved by adherence to a standard. As you identify departures from the stardard, get them fixed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceecld]

that sounds to me like a DATA problem (YOUR problem) that no computer can fix"... there is no problem to fix.

im just trying to find a certain string in entry description. Here is a simple example...


1. SHELL 01/12/08 #89183631
2. HEB #5630 87905
3. EXXON 11/11/09 Houston
4. 11/12 KROGER #893253

for each entry i want to catagorise it. So if i find the word SHELL or EXXON in the description i want to create a entry that says its relating to fuel, and for HEB or KROGER i want to say its a grocery shopping.

Basically all i want to do is search each entry with a list of key phrases (SHELL, HEB etc.) to see if it exists. I would then want to know which key phrase was found so that I could then use a match type function to call out the appropriate category. This is why I was trying to use the find/serach functions as an array formula..but it doesn't look like they work in array functions.

 

[SkipVought]

Have you built a table to reference each string that you want to categorize from your Bank Statement Download? Please post an example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I construced the following table...
[tt]
Item Group
SHELL Fuel
HEB Grocery
EXXON Fuel
KROGER Grocery
[/tt]
named my ranges, using the heading values, and then use this user-defined function on my sheet...

Function GroupName(s As String) As String
    Dim r As Range, n
    
    For Each r In [Item]
        n = InStr(s, r.Value)
        If n > 0 Then
            GroupName = r.Offset(0, 1).Value
            Exit For
        Else
            GroupName = ""
        End If
    Next
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceecld]

Ive attached the excel template with some entries.

In column D i would be looking to determine the category from the corresponding description in column B.

Hope this make more sense...

Thanks again

Chris

 

[ceecld]

Thanks Skip. Your code worked a treat!!