How to: Auto-pop text based on text of adjacent cell

[bobwiley]

Hello all:

I'm trying to help streamline our church records/finances that are currently stored in an Excel workbook. After copying & pasting our bank statements into the workbook, I must manually apply corresponding "category" and "account #" labels (think mint.com budget categories). Right now, each expense falls into 1 of 55 potential categories, each with a corresponding account #, and I have to go thru each of them and manually label every purchase by looking at the "Vendor".

This is what the spreadsheet looks like. Columns A-C are downloaded from the bank statements. The text in Columns D-E must be manually entered, line by line.

Date	Amount	Vendor	Category	Account
9/4/13	$24.63	POS purchase The Home Depot 1268 Springfield 847 8348 3744	Facility Supplies	2400
9/2/13	$69.34	Chck Crd purch amazon.com amzn.com/bill 483948 48	Books	1850
8/24/13	$127.46	POS purchase SAM'S Club Springfield 48 489384	Hospitality	1505
8/16/13	$85.24	Chck Crd purch Common Grounds Coffee Spring 49 3985	Coffee	2000

<tbody>
</tbody>

So ideally, I would love it if there was a way to auto-populate Columns D-E if there were certain "code words" that appeared in Column C (e.g. if "Home Depot" was contained in Column C, "Facility Supplies" and "2400" would automatically populate in Columns D and E). Even if just the "Category" column could be populated, it would help out a ton!

So if anyone has any ideas...this would help out a ton! Thanks in advance!

 

[Jeffrey Lopez]

Welcome to the Mr. Excel Message Board!

First, this will require a little leg work on your side, but should be fairly easy to get it done. I only did your four example, but I will explain how you can expand it to fit your needs.

First we need to have 3 different arrays, one for the "Key Word" to look for, one for the Category, and one for the Account:

Dim strKey(1 To 4) As String
Dim strCat(1 To 4) As String
Dim StrAcct(1 To 4) As String

As you add more things to look for, you will just change the (1 To 4) to whatever you need to scale it to, so in your example, (1 To 55)..

Next we need to define each one... The first one is a key we are going to look for, in the string. The only caveat to this is that it MUST be unique to each one, for example, you can't have Coffee for one, and in another line have Coffee in it, as it will pick that up as well.

strKey(1) = "Home Depot"
strCat(1) = "Facility Supplies"
StrAcct(1) = "2400"

So as you add more, you will just increase the (1) to the next number in the sequence. You can see in the full code below all 4 examples I included.

Once we have those we can get started on the Loop. We are going to start by Looping though the values in Column C. If you need to change it, this is where you would need to change where your values are stored.

For Each rngCell In Range("C2:C" & lngLstRow)

Next we are going to do another loop, and this is where we are going to look for the values that you have stored in the strKey(#). You don't need to change anything here, as I am just pulling the "max" value from the numbers set above:

    For jc = 1 To UBound(strKey)

Now, we get into the meat. We are going to start by looking "In the string" for the value that you set in strKey(#). I am using Instr, which returns a numerical values of the starting point of the phrase we are looking for... so in your first example, Home Depot would return a value of 18, or where it starts in the string, so I am just checking to see if that InSTR value is greater than 0, or if it finds it in the string...

    If InStr(rngCell.Value, strKey(jc)) > 0 Then

If it finds the values, then we are just using offset to the right (0,1) 1 column for the Category, and (0,2) 2 columns for the Account.

        rngCell.Offset(0, 1).Value = strCat(jc)
        rngCell.Offset(0, 2).Value = StrAcct(jc)

And that is about it...

I hope it helps, and please let me know if you have any questions, and please back up your excel workbook before running this macro...

Sub AccountingSavior()

Dim rngCell As Range
Dim lngLstRow As Long
Dim strKey(1 To 4) As String
Dim strCat(1 To 4) As String
Dim StrAcct(1 To 4) As String

strKey(1) = "Home Depot"
strCat(1) = "Facility Supplies"
StrAcct(1) = "2400"

strKey(2) = "amzn.com"
strCat(2) = "Books"
StrAcct(2) = "1850"

strKey(3) = "SAM"
strCat(3) = "Hospitality"
StrAcct(3) = "1505"

strKey(4) = "Coffee"
strCat(4) = "Coffee"
StrAcct(4) = "2000"

lngLstRow = 7

For Each rngCell In Range("C2:C" & lngLstRow)
    
    For jc = 1 To UBound(strKey)
    
    If InStr(rngCell.Value, strKey(jc)) > 0 Then
        rngCell.Offset(0, 1).Value = strCat(jc)
        rngCell.Offset(0, 2).Value = StrAcct(jc)
    End If
    Next jc

Next
End Sub

 

[bobwiley]

Jeffrey,

This is amazing! Thank you so much for your help with this. I'll start working on it asap and see what kind of damage I can do


Thanks again.

 

[bobwiley]

Jeffrey,

Will tweaking this line of the code determine how much of the worksheet the macro will apply to?

lngLstRow = 7

It appears that this is the case. My guess is that I would only want that number as high as the number of newly copied & pasted rows I would want to auto-populate....and not old rows...because every once in awhile there may be an outlier item (e.g. a purchase from Home Depot that shouldn't go towards "Facility Supplies" but another category). If I just reapplied the macro to the entire sheet every time, any changes I made would revert back to the formula.

Does that sound right?

Welcome to the Mr. Excel Message Board!

First, this will require a little leg work on your side, but should be fairly easy to get it done. I only did your four example, but I will explain how you can expand it to fit your needs.

First we need to have 3 different arrays, one for the "Key Word" to look for, one for the Category, and one for the Account:

Dim strKey(1 To 4) As String
Dim strCat(1 To 4) As String
Dim StrAcct(1 To 4) As String

As you add more things to look for, you will just change the (1 To 4) to whatever you need to scale it to, so in your example, (1 To 55)..

Next we need to define each one... The first one is a key we are going to look for, in the string. The only caveat to this is that it MUST be unique to each one, for example, you can't have Coffee for one, and in another line have Coffee in it, as it will pick that up as well.

strKey(1) = "Home Depot"
strCat(1) = "Facility Supplies"
StrAcct(1) = "2400"

So as you add more, you will just increase the (1) to the next number in the sequence. You can see in the full code below all 4 examples I included.

Once we have those we can get started on the Loop. We are going to start by Looping though the values in Column C. If you need to change it, this is where you would need to change where your values are stored.

For Each rngCell In Range("C2:C" & lngLstRow)

Next we are going to do another loop, and this is where we are going to look for the values that you have stored in the strKey(#). You don't need to change anything here, as I am just pulling the "max" value from the numbers set above:

    For jc = 1 To UBound(strKey)

Now, we get into the meat. We are going to start by looking "In the string" for the value that you set in strKey(#). I am using Instr, which returns a numerical values of the starting point of the phrase we are looking for... so in your first example, Home Depot would return a value of 18, or where it starts in the string, so I am just checking to see if that InSTR value is greater than 0, or if it finds it in the string...

    If InStr(rngCell.Value, strKey(jc)) > 0 Then

If it finds the values, then we are just using offset to the right (0,1) 1 column for the Category, and (0,2) 2 columns for the Account.

        rngCell.Offset(0, 1).Value = strCat(jc)
        rngCell.Offset(0, 2).Value = StrAcct(jc)

And that is about it...

I hope it helps, and please let me know if you have any questions, and please back up your excel workbook before running this macro...

Sub AccountingSavior()

Dim rngCell As Range
Dim lngLstRow As Long
Dim strKey(1 To 4) As String
Dim strCat(1 To 4) As String
Dim StrAcct(1 To 4) As String

strKey(1) = "Home Depot"
strCat(1) = "Facility Supplies"
StrAcct(1) = "2400"

strKey(2) = "amzn.com"
strCat(2) = "Books"
StrAcct(2) = "1850"

strKey(3) = "SAM"
strCat(3) = "Hospitality"
StrAcct(3) = "1505"

strKey(4) = "Coffee"
strCat(4) = "Coffee"
StrAcct(4) = "2000"

lngLstRow = 7

For Each rngCell In Range("C2:C" & lngLstRow)
    
    For jc = 1 To UBound(strKey)
    
    If InStr(rngCell.Value, strKey(jc)) > 0 Then
        rngCell.Offset(0, 1).Value = strCat(jc)
        rngCell.Offset(0, 2).Value = StrAcct(jc)
    End If
    Next jc

Next
End Sub

 

[Jeffrey Lopez]

Sorry about that... When I was type'n it out I was debugging and had to change it to a static number. You can use:

lngLstRow = ActiveSheet.UsedRange.Rows.Count

That should fix you up!