Comparing two ranges and highlight cells not found

Hello Experts,

I have been trying to compare two ranges and highlight a cell if it is not found in Range A and vice versa.

I need something that will not take a lot of calculation time as everything I have tried has slowed the spreadsheet to a crawl.

The actual ranges I am working with contains 228 columns but I have included a small sample of what I have been trying to.
UPLOAD.xls
Sonia BowditchInformation Security OfficerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NBVCConnect With a Mentor Commented:
Here is your workbook back with formatting in place.

I used your named ranges Range_A and Range_B
Copy-of-UPLOAD.xls
0
 
NBVCCommented:
Try this conditional formula after removing your current ones, and selecting columns A:C  

=AND(A1<>"",COUNTIF($A:$C,A1)-COUNTIF(A:A,A1)=0)

adjust ranges for the 288 column.

this will highlight the cell if the content is not found in any other column
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Hello NB_VC,

Thanks for getting back to me.  I can't use conditional formatting as Range A is on sheet 1 and Range B is on sheet 2.

I also need to ignore any blank cells when comparing the ranges.

Thanks in advance
Infosec36.
0
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.

 
Sonia BowditchInformation Security OfficerAuthor Commented:
Apologies,

The solution should take into account the numbers can be found in other columns.

Thanks
Infosec36
0
 
NBVCCommented:
Oh, I didn't notice the 2 sheets.

Try:

=AND(A1<>"",COUNTIF('RANGE B'!$A:$C,A1)=0)

or you can name your range in 'RANGE B'!$A:$C  and use that name

e.g

=AND(A1<>"",COUNTIF(MyRangeB,A1)=0)

and then do similarly in sheet b for MyRangeA
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Sorry NB_VC,

I can't get it to work as conditional formatting and if I try to use it as form of lookup it slows the spreadsheet down to a crawl.

Thanks
InfoSec36
0
 
stevepcguyCommented:
I was trying to follow NB_VC's advise as well, and encountering the same problem.

Obviously, conditional formatting cannot compare to cells on another worksheet. A nasty little dialog box will inform you of that.

Entering that as a form of lookup WILL suck up resources, since it is essentially going through EVERY cell and comparing it to every other cell on the other worksheet. It may work for smaller worksheets, but the calculations grow exponentially as the size of the ranges grow.

I'm not sure there's a solution that won't draw a lot of resources.
0
 
NBVCCommented:
Did you try naming the range in Range B sheet?

Select all the columns of data, and simply type a name for it in the NameBox to the left of the Formula Bar... say you name that MyRangeB

then select the columns in Range A sheet, and apply conditional format formula:

=AND(A1<>"",COUNTIF(MyRangeB,A1)=0)

it shouldn't give any errors.
0
 
yuppyduCommented:
Try this, hope I understood your needs.
UPLOAD.xls
0
 
Sonia BowditchInformation Security OfficerAuthor Commented:
Thank you for your help.  The solution works perfectly.  The problem appears to be the spreadsheet I am working with.  

I will adjust my spreadsheet so that your solution works.

Thanks again.
InfoSec36
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.

All Courses

From novice to tech pro — start learning today.