Solved

Comparing two ranges and highlight cells not found

Posted on 2013-11-19
10
323 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

776 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