Auto Select - Drop Down Option

[shanenavy26]

Here is the Sample that I will be referencing: https://www.dropbox.com/s/qsnzojulgipugma/Test.xlsm?dl=0

In this financial planner, I have data in sheet one that gets moved over to sheet 2. When this happens, it auto updates the text to remove all the repeating data. (card number). I have made rest of the process automatic except for needing to use the drop down list I created and choose a category for each expense. You can see that in the C- Column.

**Please note, you have to copy and "PasteAsValue" Otherwise the categories don't work. (unless you know a fix for that too).**

My question is: There are many expenses reoccurring every month, such as the same restaurant, or same store, and bills.
how can I get specific strings to auto choose a category. Such as in the Sample, I made "TACOBELL" and "WALMART". When I paste over the expenses, id like a VBA or some other method to be able to find taco bell, and auto choose the Fast Food category, and for Walmart to auto choose the store category.

I would also like the ability to add and edit these strings so I can filter more of these expenses later. Thank you!!

 

[shanenavy26]

Bump

 

[Fluff]

If you create a lookup table such as this

Excel 2013/2016
	G	H
3	WALMART	Stores
4	TACOBELL	Fast Food
5	Expense	Bills
6	Credit	Income
Jan Auto

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   Dim Cat As Variant
   Dim s As String
   If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
   Application.EnableEvents = False
   Target.Replace "ACH Transaction - ", "", xlPart, , False, , False, False
   Target.Replace "POS Debit - Visa Check Card XXXX - ", "", xlPart, , False, , False, False
   For Each Cl In Intersect(Target, Columns("B"))
      s = Split(Cl.Value)(0)
      Cat = Application.Index(Range("[COLOR=#ff0000]G3:H6[/COLOR]"), Application.Match(s, Range("[COLOR=#ff0000]G3:G6[/COLOR]"), 0), 2)
      If Not IsError(Cat) Then Cl.Offset(, 1).Value = Cat
   Next Cl
   Application.EnableEvents = True
End Sub

Change ranges in red to suit

 

[shanenavy26]

Thanks Fluffy. Will this work for generic things as well? Such as this transaction: [TABLE="width: 320"]
<colgroup><col></colgroup><tbody>[TR]
[TD]FARM FRESH 543 CITY ST
Will it still work if I put just "Farm Fresh" in G? or does it need to be word for word.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

At the moment it is matching on the first word, so as long as you only have one transaction type that starts with "Farm", you can use that.

 

[shanenavy26]

https://www.dropbox.com/s/al9ee7e6p31e6du/BIG TEST 1.xlsm?dl=0

I added a Reference List and Catagory like your example U2:V9. Then PasteAsValue the info from Sheet1 to March.

Nothing happened, and the Auto Replace didnt function either. I believe I messed up the reference?

 

[shanenavy26]

Actually now that I look at it. I believe the Auto Rename and Auto Category are canceling eachother out. Maybe a delay between the two would fix it?

 

[Fluff]

The first part isn't working as instead of having values like "POS Debit - Visa Check Card XXXX", the X's are now numbers.
The secnond part wasn't working as you had an error in the Index/Match function.
Try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   Dim Cat As Variant
   Dim s As String
   If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
   Application.EnableEvents = False
   Target.Replace "ACH Transaction - ", "", xlPart, , False, , False, False
   Target.Replace "POS Debit - Visa Check Card [COLOR=#ff0000]????[/COLOR] - ", "", xlPart, , False, , False, False
   For Each Cl In Intersect(Target, Columns("B"))
      [COLOR=#0000ff]If Not Cl.Value = "" Then[/COLOR]
         s = Split(Cl.Value)(0)
         Cat = Application.Index(Range("U2:V9"), Application.Match(s, Range("[COLOR=#ff0000]U2:U9[/COLOR]"), 0), 2)
         If Not IsError(Cat) Then Cl.Offset(, 1).Value = Cat
      [COLOR=#0000ff]End If[/COLOR]
   Next Cl
   Application.EnableEvents = True
End Sub

I've also added the part in blue, incase there is a blank in Col B.
Also with that data your lookup table needs to be like

Excel 2013/2016
	U	V
1	Reference	Catagory
2	FARM	Groceries
3	AMAZON	Misc
4	NAVY	Stores
5	COX	Bills
6	USAA	Bills
7	Belvidere	Bills
8	WAL-MART	Stores
9	7-ELEVEN	Groceries
March

 

[shanenavy26]

I changed the table to how you put it.

Then copied over the code and added a second replace line like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cl As Range
Dim Cat As Variant
Dim s As String
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Replace "ACH Transaction - ", "", xlPart, , False, , False, False
Target.Replace "POS Debit - Visa Check Card 1111 - ", "", xlPart, , False, , False, False
Target.Replace "POS Debit - Visa Check Card 0000 - ", "", xlPart, , False, , False, False
For Each Cl In Intersect(Target, Columns("B"))
If Not Cl.Value = "" Then
s = Split(Cl.Value)(0)
Cat = Application.Index(Range("U2:V9"), Application.Match(s, Range("U2:U9"), 0), 2)
If Not IsError(Cat) Then Cl.Offset(, 1).Value = Cat
End If
Next Cl
Application.EnableEvents = True
End Sub

It works to auto replace the test AND categorize. However it is also making a bunch of other things "groceries".

[TABLE="width: 648"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]3/30/2018[/TD]
[TD]7-ELEVEN CITY ST[/TD]
[TD]Groceries[/TD]
[TD]1.73[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/29/2018[/TD]
[TD]EZPASSST SERVICE 877-723423423[/TD]
[TD]Groceries[/TD]
[TD]20.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/29/2018[/TD]
[TD]AMAZON MKTPLACE PM AMZNCOM B[/TD]
[TD]Misc[/TD]
[TD]0.57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/28/2018[/TD]
[TD]Deposit - DFAS-CLEVELAND[/TD]
[TD]Groceries[/TD]
[TD][/TD]
[TD]1603.18[/TD]
[/TR]
[TR]
[TD]3/27/2018[/TD]
[TD]WARRIORS MANGOLIAN CITY S[/TD]
[TD]Groceries[/TD]
[TD]27.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/27/2018[/TD]
[TD]FARM FRESH 246 CITY ST[/TD]
[TD]Groceries[/TD]
[TD]10.51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/26/2018[/TD]
[TD]FARM FRESH 246 CITY ST[/TD]
[TD]Groceries[/TD]
[TD]214.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/26/2018[/TD]
[TD]AMAZON MKTPLACE PM AMZNCOM B[/TD]
[TD]Misc[/TD]
[TD]37.96[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/26/2018[/TD]
[TD]FARM FRESH 246 CITY ST[/TD]
[TD]Groceries[/TD]
[TD]17.81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/23/2018[/TD]
[TD]7-ELEVEN CITY ST[/TD]
[TD]Groceries[/TD]
[TD]14.72[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/22/2018[/TD]
[TD]BEST BUY PAYMENT 9817237198[/TD]
[TD]Groceries[/TD]
[TD]27.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/22/2018[/TD]
[TD]LAUNDRY CARD ADD V HTTPS WWW[/TD]
[TD]Groceries[/TD]
[TD]20.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

I don't get that, I get

Excel 2013/2016
	A	B	C	D
3	30-Mar-18	7-ELEVEN CITY ST	Groceries	1.73
4	29-Mar-18	EZPASSST SERVICE 877-723423423		20
5	29-Mar-18	AMAZON MKTPLACE PM AMZNCOM B	Misc	0.57
6	28-Mar-18	Deposit - DFAS-CLEVELAND
7	27-Mar-18	WARRIORS MANGOLIAN CITY S		27.3
8	27-Mar-18	FARM FRESH 246 CITY ST	Groceries	10.51
9	26-Mar-18	FARM FRESH 246 CITY ST	Groceries	214.03
10	26-Mar-18	AMAZON MKTPLACE PM AMZNCOM B	Misc	37.96
11	26-Mar-18	FARM FRESH 246 CITY ST	Groceries	17.81
12	23-Mar-18	7-ELEVEN CITY ST	Groceries	14.72
13	22-Mar-18	BEST BUY PAYMENT 9817237198		27
14	22-Mar-18	LAUNDRY CARD ADD V HTTPS WWW		20
March

Have you added anything else to the lookup table?