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
6
Medium Priority
?
252 Views
Last Modified: 2012-05-12
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 50

Accepted Solution

by:
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

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

thanks,
0
 
LVL 50
ID: 36579501
Possibly. But that would be a different question, since this one has been answered.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Closing Comment

by:csehz
ID: 36579508
Thanks :-)
0
 
LVL 50

Expert Comment

by:barry houdini
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

by:csehz
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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.

722 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