New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Findinf Common entries in two columns or lists

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Findinf Common entries in two columns or lists

Postby chubbybunny » Thu Dec 08, 2011 2:46 pm

Hello,

I am trying to find the common entries between two columns in excel (2007?). One column has over 2000 entries, I think, and the other has only maybe less than a hundred. I have found outdated formulas on the web, but would like to find one that will work, or a macro, I guess.

Thanks
  • 0

chubbybunny
Rookie
 
Posts: 1
Joined: Dec 8, 2011
Reputation: 0

Re: Findinf Common entries in two columns or lists

Postby Don » Thu Dec 08, 2011 5:39 pm

Hi there!

You can use this formula:

Code: Select all
=COUNTIF(A:A,B1)


This assumes the large list is in column A and that the smaller list is in column B and starts in row 1. This will give you a count of how many times the values in column B appear in column A and, therefore, if it is in column A.

hope this helps :)
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Findinf Common entries in two columns or lists

Postby Sisyphus » Fri Dec 09, 2011 3:15 am

Hi,
You may like to consider Conditional Formatting. Set the Formula to something like this:
Code: Select all
=($A$1:$A$2000=$A1)

I need to try this to be sure of the formula (can't do that on this iPad) but the basic idea is that the current cell (A1 in the example) is compared with all the other cells in the range. The formula will return True if a duplicate is found and that cause the cell to be highlighted, for example. The matching cell isn't affected by this action, but if it is similarly formatted it will also be marked in the same way.
Have a pleasant day!
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 54 guests