Solved

Need to compare to worksheets in excel 2013

Posted on 2014-03-10
8
259 Views
Last Modified: 2014-03-13
I have 2 worksheets and need to compare worksheet A with worksheet B. I need to compare WS 2 column A with WS 1 Column A and highlight all the items in WS 2 column A that is found in WS column A. Then sort on the highlighted cells. (that I can do myself)

Am I making any sense.

I tried to take the 3 columns from WS 2 and paste it into WS 1, but it told me the cells were not the same size. Not sure is putting all in one sheet would be better.
0
Comment
Question by:rdefino
  • 4
  • 4
8 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39919330
Consider using a COUNTIF formula to detect matching items. For example, put the following formula in Worksheet B row 1:
=IF(COUNTIF('Worksheet A'!A:A,A1),"Has a match","No matches")

You can sort by the results of that formula and highlight the cells if you like.
0
 

Author Comment

by:rdefino
ID: 39919347
So Do I put it in column A cell 1 right over the data that's in that cell?

thanks
0
 
LVL 81

Expert Comment

by:byundt
ID: 39919355
Put the formula in a blank column in row 1. Copy the formula down.

If you wanted highlighting but not sorting, then you could use this formula for Conditional Formatting:
=COUNTIF('Worksheet A'!A:A,A1)

If you post a sample workbook, I'd be glad to put the formulas in there for you.
0
 

Author Comment

by:rdefino
ID: 39919373
Hi, Here are a sample os worksheet 1 and 2.

Thanks
worksheet1.xlsx
worksheet2.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:byundt
ID: 39919412
As it happens, in Excel 2013 you can not use Conditional Formatting to compare the two worksheets if they are in different workbooks. The previously suggested formula will work if the two worksheets are in the same workbook, however.

The attached copy of worksheet2.xlsx includes the formula for "Has a match".
worksheet2-Q28385136.xlsx
0
 

Author Comment

by:rdefino
ID: 39919929
I see this is the formula:  =IF(COUNTIF('http://filedb.experts-exchange.com/incoming/2014/03_w11/839167/[worksheet1.xlsx]Sheet1'!$A:$A,A1),"Has match","No match")

I added a new worksheet to the work book and copied WS 2 to it.

Where do I place this formula and do I place it exactly as written , or do I need to modify it?
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39920244
I put both worksheets in the same workbook and put this formula in blank column F on Sheet2:
 =IF(COUNTIF(Sheet1!$A:$A,A1),"Has match","No match")

I also applied conditional formatting to Sheet2 column A using this formula:
=COUNTIF(Sheet1!$A:$A,$A1)
worksheet1-Q28385136.xlsx
0
 

Author Closing Comment

by:rdefino
ID: 39928157
Worked awesome...thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Microsoft 365 versus MicroSoft 2013/2016 8 70
Excel 2016 formulas 5 32
Manually enter date in datepicker 24 36
Excel - Duplicate values in columns 4 16
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now