[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2012-04-05
4
Medium Priority
?
322 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 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