asked on # 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

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

Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

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.

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.

Apologies,

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

Thanks

Infosec36

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

Thanks

Infosec36

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

Try:

=AND(A1<>"",COUNTIF('RANGE

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

e.g

=AND(A1<>"",COUNTIF(MyRang

and then do similarly in sheet b for MyRangeA

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

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

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.

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.

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.

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(MyRang

it shouldn't give any errors.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Try this, hope I understood your needs.

UPLOAD.xls

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

I will adjust my spreadsheet so that your solution works.

Thanks again.

InfoSec36

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

adjust ranges for the 288 column.

this will highlight the cell if the content is not found in any other column