Solved

Comparing two ranges and highlight cells not found

Posted on 2013-11-19
10
325 Views
Last Modified: 2013-11-19
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
0
Comment
Question by:Sonia Bowditch
10 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39659324
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
 

Author Comment

by:Sonia Bowditch
ID: 39659421
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
 

Author Comment

by:Sonia Bowditch
ID: 39659424
Apologies,

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

Thanks
Infosec36
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

Expert Comment

by:NBVC
ID: 39659436
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
 

Author Comment

by:Sonia Bowditch
ID: 39659569
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
 
LVL 8

Expert Comment

by:stevepcguy
ID: 39659595
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39659613
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
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39659623
Here is your workbook back with formatting in place.

I used your named ranges Range_A and Range_B
Copy-of-UPLOAD.xls
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39659800
Try this, hope I understood your needs.
UPLOAD.xls
0
 

Author Closing Comment

by:Sonia Bowditch
ID: 39659812
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

733 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