Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Comparing two ranges and highlight cells not found

Posted on 2013-11-19
10
Medium Priority
?
330 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
Independent Software Vendors: 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 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