?
Solved

Need to compare to worksheets in excel 2013

Posted on 2014-03-10
8
Medium Priority
?
274 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
[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
  • 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
Office 365 Training for Admins - 7 Day Trial

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.

 

Author Comment

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

Thanks
worksheet1.xlsx
worksheet2.xlsx
0
 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 …
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

770 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