[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Substitue Values in Excel

Posted on 2011-10-13
8
Medium Priority
?
271 Views
Last Modified: 2012-06-27
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
Comment
Question by:daintysally
  • 3
  • 3
  • 2
8 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 36966227
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
 

Author Comment

by:daintysally
ID: 36966245
There are 10 possible scenarios.  Can you tell me how to incorporate the conditions in the code?
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 36966258
Could you please post an example file and the possible scenarios and I will put something together

Michael
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 36966443
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36966507
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
 

Author Comment

by:daintysally
ID: 36970056
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
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36970281
@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
 

Author Closing Comment

by:daintysally
ID: 36973800
Thanks Dave!!   This solution works like a charm!!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question