[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
Medium Priority
322 Views
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.

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

Many thanks!
Example.xls
0
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
• 2

LVL 50

Accepted Solution

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

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

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

How to add rank to Pivot Table

New file with rank attached
Book1.xls
0

Author Closing Comment

ID: 37823025
This worked like a charm!

Thanks both of you for answering!
0

## Featured Post

Question has a verified solution.

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