• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Substitue Values in Excel

Hi Experts,

I am trying to validate data in two columns in Excel.  I have some existing code that compares the values of columns A & B of the worksheet and highlights the values that are not matching.  What is in column A, but not in column B gets highlighted red in column A and what is in column B but not in A gets highlighted yellow.  What I am faced with now is that there are certain values in column A that are equivalent to the values in column B, but they are not the same text.  For example; cell A4 may have 'Question1' as the value and cell B16 may have 'Answer1' as the value.  I need excel to understand this and not apply conditional formatting.  Can someone please tell me what code will I need to add (perhaps another if statement?) to the existing code below to do this?

For Each mycell In wks.Range("A2:A" & lastRowA)
        If mycell.Value <> "" Then
            matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("B2:B" & lastRowB), 0)
            If Err.Number <> 0 Then
                mycell.Interior.Color = vbRed
                Err.Clear
            End If
        End If
    Next mycell
   
    For Each mycell In wks.Range("B2:B" & lastRowB)
        If mycell.Value <> "" Then
            matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("A2:A" & lastRowA), 0)
            If Err.Number <> 0 Then
                mycell.Interior.Color = vbYellow
                Err.Clear
            End If
        End If
    Next mycell
 
0
daintysally
Asked:
daintysally
  • 3
  • 3
  • 2
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
Unfortunately code can only do what it is told. To do what you are asking you would have anticipate all possible scenarios where the cells are equivalent and then place conditions into the code for each scenario.

So unless there is a very limited number of equivalent matches then what you are asking is not realistically feasible

Michael
0
 
daintysallyAuthor Commented:
There are 10 possible scenarios.  Can you tell me how to incorporate the conditions in the code?
0
 
Michael FowlerSolutions ConsultantCommented:
Could you please post an example file and the possible scenarios and I will put something together

Michael
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dlmilleCommented:
In future, please select "ASK A RELATED QUESTION" or post your last example, so E-E experts have a better starting position http:/Q_27391964.html...

Proposed solution:
I suggest perhaps a lookup table for comparisions of corollaries from A to B (or B to A) so those are also compared.

E.g.,

IFTHIS    THENthisIStheSAME
Question1  Answer1
Answer1    Question1

Then you'd only need to add one more condition, in addition to the match.

Assuming that table was called COROLLARY (for demonstration purposes), here's your code:
Sub inAnotB()
Dim wkb As Workbook
Dim wks As Worksheet
Dim matchValue As Long
Dim lastRowA As Long, lastRowB As Long
Dim foundDup, foundCorly As Boolean

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    lastRowA = wks.Range("A" & wks.Rows.Count).End(xlUp).Row
    lastRowB = wks.Range("B" & wks.Rows.Count).End(xlUp).Row
    
    
    On Error Resume Next
    For Each mycell In wks.Range("A1:A" & lastRowA)
        foundDup = False
        If mycell.Value <> "" Then 'if nonblank cell, then compare
            
            matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("B1:B" & lastRowB), 0)
            If Err.Number <> 0 Then 'didn't find an exact match
                Err.Clear
                matchValue = Application.WorksheetFunction.VLookup(mycell.Value, [COROLLARY], 2, 0)
                If Err.Number <> 0 Then 'didn't find a lookup match to corollary table
                    mycell.Interior.Color = vbRed
                    Err.Clear
                End If
            End If
        End If
    Next mycell
    
    For Each mycell In wks.Range("B1:B" & lastRowB)
        If mycell.Value <> "" Then 'if nonblank cell, then compare
            
            matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("A1:A" & lastRowA), 0)
            If Err.Number <> 0 Then 'didn't find an exact match
                Err.Clear
                matchValue = Application.WorksheetFunction.VLookup(mycell.Value, [COROLLARY], 2, 0)
                If Err.Number <> 0 Then 'didn't find a lookup match to corollary table
                    mycell.Interior.Color = vbRed
                    Err.Clear
                End If
            End If
        End If
    Next mycell

End Sub

Open in new window


See attached demo workbook:

inAnotB-r3.xls
0
 
dlmilleCommented:
I had a couple variables in the above posting, I didn't end up using, so you can remove lines 6 & 17.

For fun, I did a slight optimization, putting the work in a subroutine that's called by the main routine - as the two loops are doing the same work, just on the opposite columns.

Here's the final code:

 
Sub inAnotB()
Dim wkb As Workbook
Dim wks As Worksheet

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    Call markNoMatch(wks, "A", "B", 1, [COROLLARY], vbRed)
    Call markNoMatch(wks, "B", "A", 1, [COROLLARY], vbRed)

End Sub
Sub markNoMatch(wks As Worksheet, colCompare As String, colCompare2 As String, startRow As Long, lookup_array As Range, lcolorMark As Long)
'routine searches colCompare2 with each element from colCompare.  If a match is not found, then a final
'check lookup up against the table lookup_array is done, to see if there are corollary values that would also
'qualify as a match
'If a match is not found, the cell is marked with lColorMark interior fill color
Dim myCell As Range
Dim lastRow As Long
Dim lastRow2 As Long
Dim matchValue As Long

    lastRow = wks.Range(colCompare & wks.Rows.Count).End(xlUp).Row
    lastRow2 = wks.Range(colCompare2 & wks.Rows.Count).End(xlUp).Row
    
    For Each myCell In wks.Range(colCompare & startRow, colCompare & lastRow)
    
        If myCell.Value <> "" Then 'if nonblankcell, then compare
            On Error Resume Next
            matchValue = Application.WorksheetFunction.Match(myCell.Value, wks.Range(colCompare2 & startRow & ":" & colCompare2 & lastRow2), 0)
            If Err.Number <> 0 Then 'didn't find an exact match
                Err.Clear
                matchValue = Application.WorksheetFunction.VLookup(myCell.Value, lookup_array, 2, 0)
                If Err.Number <> 0 Then 'didn't find a lookup match to corollary table
                    myCell.Interior.Color = lcolorMark
                    Err.Clear
                End If
            End If
        End If
    Next myCell
    
    On Error GoTo 0
            
End Sub

Open in new window


See attached demo workbook.

Enjoy!

Dave
inAnotB-r4.xls
0
 
daintysallyAuthor Commented:
Hi Dave,

Please accept my apologies for not asking a related question.  I tried your demo workbook and it still highlighted   Answer1 and Question1 in the columns.  Since those values were in the table, they should not have been highlighted, correct?  
0
 
dlmilleCommented:
@daintysally - no need for apologies! This was just for future, so your questions get a better chance by having the benefit of prior dialogue/spreadsheets, etc.,  No worries at all!  I should have said it like "gentle reminder: please ask related question..."

You are correct, my Vlookup returns a string and I was catching it in a long variable.  That's repaired.

Here's the update:

Dave
inAnotB-r5.xls
0
 
daintysallyAuthor Commented:
Thanks Dave!!   This solution works like a charm!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now