Excel 2007: Extract Text String based on a Defined List of Criteria

[justr8hr]

Hello guys,
Please help this newbie.
I want to search the vendor/Text String (from bank statement)on the list below.

KEEP THE CHANGE TRANSFER TO ACCT

POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA

STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA

ID:BX0364457 IDS PROPERTY CAS DES: PREM

POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA

TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA

CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE

KEEP THE CHANGE TRANSFER TO ACCT

POS CSMC CAFETERIA4007 12/27 PURCHASE

<tbody>
</tbody>

Initially I am doing this manually. I have decided to document my input so I don't have to input them one by one, only if I just know to use them as reference to automatically extract the text.
Here is My criteria list (the actual one is more comprehensive)

VENDOR	KIND/CATEGORY
American Ranch	Groceries
Anthem Blue	Health Insurance
Autozone	Car Repairs
Carlton Hair	Personal Care
Cheesecake Factory	Meals
Chevron	Fuel
CSMC CAFETERIA	Restaurants/Dining

<tbody>
</tbody>

Once I am able to extract the text based on the criteria, I can then put the right category.
VBA or Excel solutions will be greatly appreciated
Thanks a lot!

 

[hiker95]

justr8hr,

Welcome to the MrExcel forum.

Sample criteria data in worksheet Criteria:

Excel 2007
	A	B
1	VENDOR	KIND/CATEGORY
2	American Ranch	Groceries
3	Anthem Blue	Health Insurance
4	Autozone	Car Repairs
5	Carlton Hair	Personal Care
6	Cheesecake Factory	Meals
7	Chevron	Fuel
8	CSMC CAFETERIA	Restaurants/Dining
9
Criteria

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B
1	KEEP THE CHANGE TRANSFER TO ACCT
2	POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
3	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
4	ID:BX0364457 IDS PROPERTY CAS DES: PREM
5	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
6	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
7	CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
8	KEEP THE CHANGE TRANSFER TO ACCT
9	POS CSMC CAFETERIA4007 12/27 PURCHASE
10
Sheet1

After the macro:

Excel 2007
	A	B
1	KEEP THE CHANGE TRANSFER TO ACCT
2	POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA	Groceries
3	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
4	ID:BX0364457 IDS PROPERTY CAS DES: PREM
5	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
6	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
7	CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE	Personal Care
8	KEEP THE CHANGE TRANSFER TO ACCT
9	POS CSMC CAFETERIA4007 12/27 PURCHASE	Restaurants/Dining
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindKindCategory()
' hiker95, 05/16/2013
' http://www.mrexcel.com/forum/excel-questions/703193-excel-2007-extract-text-string-based-defined-list-criteria.html
Dim a As Variant, b As Variant, c As Variant
Dim i As Long, ii As Long
c = Sheets("Criteria").Cells(1).CurrentRegion
With Sheets("Sheet1")
  .Columns(2).ClearContents
  a = .Cells(1).CurrentRegion
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1)
    For ii = 2 To UBound(c, 1)
      If InStr(a(i, 1), UCase(c(ii, 1))) > 0 Then
        b(i, 1) = c(ii, 2)
        Exit For
      End If
    Next ii
  Next i
  .Cells(1, 2).Resize(UBound(b, 1)) = b
  .Columns(2).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindKindCategory macro.

 

[justr8hr]

You are amazing hiker95 !
Sorry if I have not made clear the output that I was expecting. The way I looked at your response is that you search the criteria list for the vendor then go over the long strings, then put the appropriate category. This by itself is amazing, worthy of praise!
Is there a way that you can tweak this a bit?
Instead of putting the category right away, can you please extract the text from the long string. If the extracted text is similar or matches the one from the Criteria List, then follow the category on that Criteria List.
Something like this:

COLUMN 1	COLUMN2	COLUMN 3
Long Text String where the vendor is to be extracted	Extracted text	Category: (if the extracted text found in column 2 is similar or matches with the Criteria List, follow the Category found on the Criteria List
		If there is no match - - - either an ERROR or an option to add to the Category List

<tbody>
</tbody>

 

[hiker95]

justr8hr,

With the same two beginning screenshots in my last reply.

After the updated macro per your latest request:

Excel 2007
	A	B	C
1	KEEP THE CHANGE TRANSFER TO ACCT	Error
2	POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA	AMERICAN RANCH	Groceries
3	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA	Error
4	ID:BX0364457 IDS PROPERTY CAS DES: PREM	Error
5	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA	Error
6	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	Error
7	CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE	CARLTON HAIR	Personal Care
8	KEEP THE CHANGE TRANSFER TO ACCT	Error
9	POS CSMC CAFETERIA4007 12/27 PURCHASE	CSMC CAFETERIA	Restaurants/Dining
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub FindKindCategoryV2()
' hiker95, 05/16/2013
' http://www.mrexcel.com/forum/excel-questions/703193-excel-2007-extract-text-string-based-defined-list-criteria.html
Dim a As Variant, b As Variant, c As Variant
Dim i As Long, ii As Long, e As Long
c = Sheets("Criteria").Cells(1).CurrentRegion
With Sheets("Sheet1")
  .Columns("B:C").ClearContents
  a = .Cells(1).CurrentRegion
  ReDim b(1 To UBound(a, 1), 1 To 2)
  For i = 1 To UBound(a, 1)
    e = 0
    For ii = 2 To UBound(c, 1)
      If InStr(a(i, 1), UCase(c(ii, 1))) = 0 Then
        e = e + 1
      ElseIf InStr(a(i, 1), UCase(c(ii, 1))) > 0 Then
        b(i, 1) = UCase(c(ii, 1))
        b(i, 2) = c(ii, 2)
        Exit For
      End If
      If e > 0 Then
        b(i, 1) = "Error"
      End If
    Next ii
  Next i
  .Cells(1, 2).Resize(UBound(b, 1), UBound(b, 2)) = b
  .Columns("B:C").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindKindCategoryV2 macro.

 

[justr8hr]

You rock hiker95, kudos!
i tested it with the data I sent you and it worked.
i copied many times the same files and run the macro and it worked
however when I added some items on the criteria sheet then added exactly the same item on the source sheet,
the macro returned an error as if those items are not in the criteria sheet

pls extend me more of ur patience thanks

 

[hiker95]

justr8hr,

Thanks for the feedback.

You are very welcome. Glad I could help.

however when I added some items on the criteria sheet then added exactly the same item on the source sheet,
the macro returned an error as if those items are not in the criteria sheet
pls extend me more of ur patience thanks

In order to get it right the next time I will need the workbook that the macro failed on.

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[justr8hr]

Hello hiker95 Per your advice I opened a Box account and tried to make the link (I just hope it works, I am no savvy!). The file is a macro-enabled workbook. One of the sheets is named "Criteria" where I put now the more populated list, which will eventually grow. So please indulge me with an option to populate it as may be required. Here's the link and thank you so much for your time and intellect: https://www.box.com/s/umebswve02vfjtqtu2o5

 

[hiker95]

justr8hr,

Thanks for the workbook.

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C
1	KEEP THE CHANGE TRANSFER TO ACCT
2	POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
3	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
4	ID:BX0364457 IDS PROPERTY CAS DES: PREM
5	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
6	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
7	CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
8	KEEP THE CHANGE TRANSFER TO ACCT
9	POS CSMC CAFETERIA4007 12/27 PURCHASE
10	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
11	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
12	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
13	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
14	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
15	ID:BX0364457 IDS PROPERTY CAS DES: PREM
16	KEEP THE CHANGE TRANSFER TO ACCT
17	POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
18	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
19	ID:BX0364457 IDS PROPERTY CAS DES: PREM
20	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
21	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
22	CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
23	KEEP THE CHANGE TRANSFER TO ACCT
24	POS CSMC CAFETERIA4007 12/27 PURCHASE
25	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
26	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
27	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
28	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
29	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
30	ID:BX0364457 IDS PROPERTY CAS DES: PREM
31	Albertsons
32	Chevron
33
Sheet1

After the new macro:

Excel 2007
	A	B	C
1	KEEP THE CHANGE TRANSFER TO ACCT	KEEP THE CHANGE TRANSFER TO ACCT
2	POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA	Error
3	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA	STARBUCKS	Meals & Entertainment
4	ID:BX0364457 IDS PROPERTY CAS DES: PREM	Error
5	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA	MACY'S	CLOTHES/SHOES
6	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
7	CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE	Error
8	KEEP THE CHANGE TRANSFER TO ACCT	KEEP THE CHANGE TRANSFER TO ACCT
9	POS CSMC CAFETERIA4007 12/27 PURCHASE	Error
10	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA	MACY'S	CLOTHES/SHOES
11	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
12	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
13	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA	STARBUCKS	Meals & Entertainment
14	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
15	ID:BX0364457 IDS PROPERTY CAS DES: PREM	Error
16	KEEP THE CHANGE TRANSFER TO ACCT	KEEP THE CHANGE TRANSFER TO ACCT
17	POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA	Error
18	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA	STARBUCKS	Meals & Entertainment
19	ID:BX0364457 IDS PROPERTY CAS DES: PREM	Error
20	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA	MACY'S	CLOTHES/SHOES
21	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
22	CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE	Error
23	KEEP THE CHANGE TRANSFER TO ACCT	KEEP THE CHANGE TRANSFER TO ACCT
24	POS CSMC CAFETERIA4007 12/27 PURCHASE	Error
25	POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA	MACY'S	CLOTHES/SHOES
26	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
27	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
28	STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA	STARBUCKS	Meals & Entertainment
29	TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA	TARGET	GROCERIES
30	ID:BX0364457 IDS PROPERTY CAS DES: PREM	Error
31	Albertsons	ALBERTSONS	GROCERIES
32	Chevron	CHEVRON	FUEL
33
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub FindKindCategoryV3()
' hiker95, 05/18/2013
' http://www.mrexcel.com/forum/excel-questions/703193-excel-2007-extract-text-string-based-defined-list-criteria.html
Dim a As Variant, b As Variant, c As Variant
Dim i As Long, ii As Long, e As Long, lr As Long
With Sheets("Criteria")
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  c = .Range(.Cells(2, 1), .Cells(lr, 2))
End With
With Sheets("Sheet1")
  .Columns("B:C").ClearContents
  a = .Cells(1).CurrentRegion
  ReDim b(1 To UBound(a, 1), 1 To 2)
  For i = 1 To UBound(a, 1)
    e = 0
    For ii = 2 To UBound(c, 1)
      If InStr(UCase(a(i, 1)), UCase(c(ii, 1))) = 0 Then
        e = e + 1
      ElseIf InStr(UCase(a(i, 1)), UCase(c(ii, 1))) > 0 Then
        b(i, 1) = UCase(c(ii, 1))
        b(i, 2) = c(ii, 2)
        Exit For
      End If
      If e > 0 Then
        b(i, 1) = "Error"
      End If
    Next ii
  Next i
  .Cells(1, 2).Resize(UBound(b, 1), UBound(b, 2)) = b
  For i = 1 To UBound(b, 1)
    If .Cells(i, 2) = "Error" Then
      .Cells(i, 2).Interior.Color = 255
    End If
  Next i
  .Columns("B:C").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindKindCategoryV3 macro.


I have examined the results after the macro in worksheet Sheet1, and, can not determine the logic for the Error's in column B, to be able to pick the correct part of the respective string in column A, to be moved to worksheet Criteria. Each string has a different structure?

 

[justr8hr]

Thanks a lot hiker95

yes each source string has different structure because they were extracts from bank statements.
Kinda weird because those that appeared as errors were identified on your macro version 2!

 

[hiker95]

justr8hr,

You are very welcome. Glad I could help.