Solved

Comparing two ranges and highlight cells not found

Posted on 2013-11-19
10
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

724 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