Solved

Need to compare to worksheets in excel 2013

Posted on 2014-03-10
8
257 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 80

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 80

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 80

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 80

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

17 Experts available now in Live!

Get 1:1 Help Now