Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Excel 2010 - Count common elements

Posted on 2011-09-22
Medium Priority
252 Views
Dear Experts,

Can you please have a look at the attached file, it contains small data on Sheet1 and Sheet2, both in A column.

Could you advise which formula could give back a number that how many common elements are on the two sheets in column A? In the example this would be 3, because only the 101,102 and 107 are on both sheets

thanks,
CommonInColumn.xlsx
0
Question by:csehz
[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
• 3
• 2

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36579470
Hello,

try

=SUM(--(NOT(ISERROR(MATCH(A2:A7,Sheet1!A2:A5,0)))))

This is an Array formula and needs to be entered with Ctrl-Shift-Enter

Or a non-array formula, confirmed with a simple Enter

=SUMPRODUCT(--(NOT(ISERROR(MATCH(A2:A7,Sheet1!A2:A5,0)))))

cheers, teylyn
0

LVL 1

Author Comment

ID: 36579495
Teylyn thanks very much it works, can I put such array formula also to VBA?

thanks,
0

LVL 50

Expert Comment

ID: 36579501
Possibly. But that would be a different question, since this one has been answered.
0

LVL 1

Author Closing Comment

ID: 36579508
Thanks :-)
0

LVL 50

Expert Comment

ID: 36579513
Can there be duplicates? For example if A6 became 102 there would still arguably be 3 common elements......but teylyn's formulas would return 4?

regards, barry
0

LVL 1

Author Comment

ID: 36579523
Barry well for me fortunately on the live files the columns sure do not have duplicates. So I even did not think to such scenario, however yes can be a good next question too :-)
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month9 days, 19 hours left to enroll