Need to compare to worksheets in excel 2013

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.
rdefinoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
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
 
byundtCommented:
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
 
rdefinoAuthor Commented:
So Do I put it in column A cell 1 right over the data that's in that cell?

thanks
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
byundtCommented:
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
 
rdefinoAuthor Commented:
Hi, Here are a sample os worksheet 1 and 2.

Thanks
worksheet1.xlsx
worksheet2.xlsx
0
 
byundtCommented:
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
 
rdefinoAuthor Commented:
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
 
rdefinoAuthor Commented:
Worked awesome...thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.