Match with conditional format in excel 2003

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello Internet,

I am in a bind here as I have exhausted my excel knowledge on this problem. I am looking for a way to highlight (in red) a cell which contains text that does not match text in another.

My spreadsheet has a layout as thus, A row of names is located on row 5 and extends all the way to column IV (as far as excel will allow). I have named this field "names".

These names are repeated in a grid, named "Grid" on another sheet, the whole grid is named, including the headers and the descriptions down the side.

I have used this code : =NOT(ISERROR(MATCH(C$5,GRID,0))) in each cell of the NAMES field

and have also used various forms of a LOOKUP in some attempt at making things work but to no avail.

Imagine if you will that my 200+ names are forever being renamed or removed altogether from the grid and so I have a need to visually differentiate between names which are being matched to the grid and names which need to be altered on the "NAMES" field in order to for both data sources to relate. Here is the clincher though, I cannot simply point 1 name to another as the "GRID" is actually used to monitor where a person is working week on week and thus, names are moved around within the grid!

What I need, had excel learned to speak English, is a format which says:

"Match the text (name) in this cell to any cell within this "GRID", if there isn't a match then turn the cell in "NAMES" red. If there is a match, don't worry".

I hope I have made myself clear, and that the code is yet another thing I can learn (as opposed to something stupid I didn't think of)


6 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Well I am sure there is other way to do it too. This is a proposed solution for your two issues
1. Indicate which name is not present in the GRID
2. If name is present, location of that person

I am proposing two functions.
A. One function isPresent(range, string) returns true or false depending if the name was found in the GRID or not
B. The other function PresentAt(range, range, string) returns a string. This string will either contain the location name or "Not Available" if the name is not found

Step1

1. Create a new Named range "LOCATION". This is the name of various location you have on your sheet2, row 2. This range should start from the same column where the GRID name range starts and should end at the same column where the GRID named range ends


2. Open VBE by pressing ATL + F11 and then inserting a new module

3. Paste the code below ( after the instructions)

4, Once the code has been pasted. Select the row containing the names on sheet1

5 Go to Conditional formatting and enter this formula
=NOT(OR(A$5="",ispresent(GRID,A$5)))
NOTE : A$5 is based on your sample book, In that the names were on ROW 5. Change it to what ever rows they actually are

6 Go to format under conditional formatting and pick the color of your choice

Here is the code
Function isPresent(NameGrid As Range, lookup As String) As Boolean
'NameGRID: the grid that contain names and location of person
'lookup  : name that is to be searched in GRID

Dim temp As String

    ' to upper case as we would be comparing the names latter and
    ' want to take out any issues due to upper or lower cases of
    ' alphabets
    temp = UCase(lookup)

    'default the name is not present
    isPresent = False
    
    ' looping thru each cells in GRID
    For Each cell In NameGrid
    
        'comparing names
        If UCase(cell.Value) = temp Then
            
            'name has been matced
            isPresent = True
            Exit Function
        End If
    Next
    
End Function

Function PresentAt(LocationGrid As Range, NameGrid As Range, lookup As String) As String
'LocationGrid: the named ranged that has the locations only.
'NameGRID: the grid that contain names and location of person
'lookup  : name that is to be searched in GRID

Dim temp As String
Dim lItem As Long
Dim Locations As Long
    
    temp = UCase(lookup)
    
    'default
    PresentAt = "Not Available"
    lItem = 0 ' array pointer
    Locations = LocationGrid.Cells.Count 'number of cells in the locations grid
    
    For Each cell In NameGrid
        lItem = lItem + 1
        If UCase(cell.Value) = temp Then
            PresentAt = LocationGrid.Cells(1, lItem)
            Exit Function
        End If
        
        If lItem = Locations Then lItem = 0
    Next
End Function
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Come to think off it. You need only one function that can do both for you

Use this for condtional formatting
=NOT(OR(A$5="",presentAt(LOCATIONS,GRID,A$5)<>"Not Available"))

In this case you do not need any isPresent function and only PresentAt is needed than
I have just tried this same formula for my work and it doesn't work, the location based one i mean.

I kept all ranges with the same names as defined in your answer and the result is "true", didn't the person who asked the question want to return the "location" of the value? If not then i do, how do i return the column header for a value?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Did you use the function PresentAt?
Yes, i used it with the cond format and it did nothing, then i used it as a straight formula in a cell and it returned "true" into the cell, even if i use it as a cond format in the cell i want to locate it does nothing.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What formula are you typing in the cell.

in cell you should have some thing like this


=PresentAt( LocGrid, NameGrid, A2)
where LocGrid is the name of the range for location
NameGrid is the name of the range that stores names
and A2 is just an example saying that I want to search for the value this is found in cell A2


If it still does not work, post your formula here
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please post a sample book at some share site like https://authentification.site and post the link here.

The link takes me to a "forbidden server" message. I tried to post an upload with this very site when I first posted but I got the same thing when I tried to upload before. I shall try using IE rather than Google Chrome to get to the site but if that fails, is there any other option? I could make my description clearer?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Strange, I was able to go. You have been very clear. It is just that i find picture speaks louder than the words. Could you mail me the file at "rizvisa at hotmail dot com" . Hopefully no virus, no Trojans or worms :P

That worked fine thanks.

I would be interested to know why an "address(match..." function wouldn't work for this. I have had some luck with this in the past but this time it didn't work.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It was a multidimensional array (YOUR GRID). I could get match work for a single dimensional array. There might be a way, I was just unable to come up with match and multidimensional array