Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need to compare to worksheets in excel 2013

Posted on 2014-03-10
8
Medium Priority
?
279 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
Technology Partners: 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!

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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 describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

598 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