USD $40.00+ Compare Columns and Highlight.

Hoowzer · Dec 1st 2010

I have a VBA issue and would be willing to pay 40 bucks or so for your time. (more if it requires extensive amounts of your time)

I have 6 columns:

Column: Name:
A Keyword
B Campaign
C Adgroup
D Negative Keyword
E Negative Campaign
F Negative Adgroup

What I would like to happen is for a VBA code to look at columns B & C and match it with columns E and F. If the columns are identical the code will look at Column A (in the same row) and match it with column D - if the Negative Keyword in column D is matched exactly or is present anywhere in column A - the code will highlight the Keyword in column A and spit out in column G which negative keyword triggered the keyword in column A to be highlighted.

Please let me know if you can do this.

I am attaching a mock up of how I envision the worksheet looking/working like that I will want the vba code to work on.

**Reafidy** · Dec 1st 2010

Re: USD $40.00+ Need advanced(i think) vba help to compare columns and highlight.

Hi Hoowzer,

As you have stated an amount you are willing to pay I have moved your thread to the Hire Help forum, you should receive a reply from a developer here soon.

AndrewMB · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

I am looking at this now, and will post a further message as soon as I have done so.

AndrewMB · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

I am starting on this, and have two questions:

1. How would you like the match in Column A highlighted?
2. You say "spit out in column G which negative keyword triggered the keyword in column A to be highlighted": as we are matching column D in the same row only, as far as I can see this can only be the contents of this cell in column D, or have I missed something?

Hoowzer · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

Quote from AndrewMB;532898

I am starting on this, and have two questions:

1. How would you like the match in Column A highlighted?
[COLOR="red"]Column A can be highlighted in Red[/COLOR]

2. You say "spit out in column G which negative keyword triggered the keyword in column A to be highlighted": as we are matching column D in the same row only, as far as I can see this can only be the contents of this cell in column D, or have I missed something?

[COLOR="red"]
So on the backend - a word is highlighted in column A only if it matches or contains a word in column D. I would like to know which word in column D made the trigger for the word in column A to be highlighted....Im open to other methods of showing this.[/COLOR]

AndrewMB · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

That's fine, I can certainly highlight it in red.

My second, question, though is this. If I am only looking at the word in column D for the same row as I am matching in column A, then it can only be this word which is causing the match. OR, do you want me to attempt to match every word in column D (i.e. all rows) against each entry in column A?

Hoowzer · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

Quote from AndrewMB;532901

That's fine, I can certainly highlight it in red.

My second, question, though is this. If I am only looking at the word in column D for the same row as I am matching in column A, then it can only be this word which is causing the match. OR, do you want me to attempt to match every word in column D (i.e. all rows) against each entry in column A?

[COLOR="red"]Basically the word in column D needs to be matched to all words in column A IF it matches the same campaign and adgroup names (column B and C need to match column E and F). [/COLOR]

AndrewMB · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

Have a look at this to see if it does what you want. Please note:

I am matching all the entries in Column A until I find a blank in Column A against all the entries in Column D until I find a blank in Column D.
I do not assume column A and column D are the same length. I do expect Columns B and C to be the same length as column A, and columns E and F to be the same length as column D.
I stop on the first match - if you want to handle multiple matches then please let me know what you would like done with them.
I noticed some entries had a trailing space, so I am trimming this off before compares or matches.
I am ignoring case when matching

The attached has the following code attached to Sheet1:

Code

Option Explicit
Option Compare Text


Sub MatchData()
    Dim I As Long, J As Long, K As Long
    Dim Matchto1 As String, Matchto2 As String, Matchto3 As String, Matchval As String
    
    'This is the main loop which evaluates every row in columns A-C until hitting an empty cell.
    'Any previous matches are cleared before starting.
    I = 2
    Do
        Matchto1 = Cells(I, 1).Value
        If Len(Matchto1) = 0 Then Exit Do
        Cells(I, 7).ClearContents
        Cells(I, 1).Font.ColorIndex = xlAutomatic
        Matchto2 = Trim(Cells(I, 2).Value)
        Matchto3 = Trim(Cells(I, 3).Value)
        
        'This will compare the values with columns D-F
        K = 2
        Do
            Matchval = Trim(Cells(K, 4).Value)
            If Len(Matchval) = 0 Then Exit Do
            If Matchto2 = Trim(Cells(K, 5).Value) Then
                If Matchto3 = Trim(Cells(K, 6).Value) Then
                    J = InStr(Matchto1, Matchval)
                    If J > 0 Then
                        Cells(I, 7).Value = Matchval
                        Cells(I, 1).Characters(Start:=J, Length:=Len(Matchval)).Font.Color = -16776961
                        Exit Do
                    End If
                End If
            End If
            K = K + 1
        Loop
        I = I + 1
    Loop
End Sub

Display More

Hoowzer · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

Wow this looks good - could you make the box highlighted rather than the text highlighted?

Also there will be some instances where there is no column F - I would just have data for Columns A-E, how would the current code treat this?

The data I have right now would be a mixture of having data for (Columns A-E or columns A-F) I would like for this code to work for both if possible....thanks for doing this so quickly!

Hope this isnt too difficult to alter.

Much Thanks!

AndrewMB · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

I've changed it to highlight the cell.

Also, if there is no value in column F it now ignores the comparison with column C - I hope this is what you want.

Hoowzer · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

Wow, this is great. Thanks so much for this. I will go and try some data sets tomorrow afternoon to see if this works. Please let me know what you would charge and how to get it to you. Cheers

AndrewMB · Dec 1st 2010

Re: USD $40.00+ Compare Columns and Highlight.

Thanks. I'm happy with the $40 you offered, paid through Paypal. I will shortly send you a private message with my Paypal information.

USD $40.00+ Compare Columns and Highlight.

Participate now!

Share