?
Solved

Return 1st & 2nd common column B numbers based on 1st & 2nd common column A numbers

Posted on 2012-04-05
4
Medium Priority
?
319 Views
Last Modified: 2012-04-09
Hi experts,

I have an excel file that I have been working for analyzing data and I have run into a wall.

I need help developing the formula in Excel to return the 1st and 2nd most frequent numbers in a column based on the 1st and 2nd most frequent numbers in another column.

Please refer to the attached spreadsheet for better explanation...

To acquire the points for this question I will need a working formula

Many thanks!
Example.xls
0
Comment
Question by:r_johnston
[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
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 37813834
You can use this array formula in H4

=MODE(IF(B5:B20=G4,C5:C20))

confirmed with CTRL+SHIFT+ENTER

...and this one in H5

=MODE(IF(B5:B20=G4,IF(C5:C20<>H4,C5:C20)))

also confirmed with CTRL+SHIFT+ENTER

Note: that second one returns 10 as that is the second most frequent with 5, I think

You can use similar formulas in H6 and H7, see attached

regards, barry
Mode.xls
0
 
LVL 1

Expert Comment

by:Bob60618
ID: 37813880
You can create a pivot table that will count them and put them in order highest to lowest of occurence. See attached file. If you need help creating pivot table, I can provide more details.

This supplies all of them and just not the top two. I need to think some more about how to get just top two.
Book1.xls
0
 
LVL 1

Expert Comment

by:Bob60618
ID: 37813985
Closer to a solution - the pivot table now has rank

How to add rank to Pivot Table
http://blogs.office.com/b/microsoft-excel/archive/2010/11/02/add-rank-to-pivottable.aspx

New file with rank attached
Book1.xls
0
 

Author Closing Comment

by:r_johnston
ID: 37823025
This worked like a charm!

Thanks both of you for answering!
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

765 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