Excel 2007 : Formula needed to check if cell data DOESN'T match data in list

**dancing-shadow** · 02-10-2011, 05:02 AM

I'm trying to sort out a formatting formula, based on the following:

=COUNTIF($A$1:$A$20,B1)

I have a list of fixed data (A1-A20, formatted as text, but some values contain numbers and letters - eg. 12F20, 429NG, 1ED48)

I need to add data in colB. If the data I enter in column B does not match any of the data in colA, I want it to format (turn red, go bold, whatever)

The formula I used above checks if the data matches, and formats it instead. I've tried

=COUNTIF($A$1:$A$20,"<>B1")

but that doesn't work either, it still formats the cell if it matches.

I'm sure it's so simple, but I just can't see it!!

**DonkeyOte** · 02-10-2011, 05:14 AM

Either:

=COUNTIF($A$1:$A$20,$B1)=0

or

=ISNA(MATCH($B1,$A$1:$A$20,0))

Above assumes case insensitive tests ... if you need exact / case sensitive matches (eg Apple <> apple) let us know

**dancing-shadow** · 02-10-2011, 05:44 AM

Hi - thanks for a fast response!!

Both codes do work, sort of...

say in ColB I've added 10 different letter/number data. The last one to go in (so line 10), shows as not matching, despite it clearly showing in my ColA list.

But when I add another cell of data underneath it in line 11, line 10 suddenly shows it matches, and the new line 11 now shows as not matching, again, despite it being in the ColA list.

This is the case for both codes - as though it looks at the cell below to see if it matches, and puts the format on the cell above. Any suggestions?

**DonkeyOte** · 02-10-2011, 05:46 AM

Clear whatever rules you have in place and start again.

Select the range from B1 down (important) and apply the rule as outlined.

**dancing-shadow** · 02-10-2011, 06:21 AM

Cleared off completely, re-entered as advised....

Still the bottom cell of data is always showing as a non-match until something is entered underneath it, and now also not matching certain info at all, despited being in the list, in the same format, same cases etc etc.

Have even tried it on a brand new worksheet,pasting in the same info (values only), and still does the same.

**davesexcel** · 02-10-2011, 06:42 AM

Hi Dancing,

I have the same problem.
But when I use it in code it works just fine.

Sub ColourCells()
    Dim LastRow As Long
    LastRow = Range("A65536").End(xlUp).Row
    Range("A1").Select
    With Range("A1:A" & LastRow).FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=isna(MATCH(A1,$B$1,0))"
        .Item(1).Interior.ColorIndex = 6
    End With

End Sub

**dancing-shadow** · 02-10-2011, 06:45 AM

Could it be cos I'm entering it in the actual formatting formula box (Cond formatting -> New Rule -> Use a formula to determine...), rather than any programming box (which I have zero xp of btw...)?

**DonkeyOte** · 02-10-2011, 06:54 AM

Post a sample file illustrating the problem.

I would add that you should also add an IF to the rule to negate blanks assuming these should be ignored but we can deal with once we have a sample.

**dancing-shadow** · 02-10-2011, 07:42 AM

Not sure what I did/was doing, but a couple of times of clearing it and re-doing and it's now working as needed

Can't really post a sample file without chopping most of it out due to sensitive info in the rest of the sheet.

Where abouts would the 'IF' go to make it ignore any blanks? I'm using the "=countif" formula, as the other one was still causing problems)

Thanks for your patience and help btw.

**DonkeyOte** · 02-10-2011, 07:52 AM

Add a pre-emptive IF along the lines of:

=IF($B1<>"",COUNTIF($A$1:$A$20,$B1))

**dancing-shadow** · 02-10-2011, 08:12 AM

super - many many many thanks

**dancing-shadow** · 02-10-2011, 08:13 AM

super - many many many thanks

Excel 2007 : Formula needed to check if cell data DOESN'T match data in list

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Re: Formula needed to check if cell data DOESN'T match data in list

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions