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

r_johnston
r_johnston used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Commented:
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

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

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

Author

Commented:
This worked like a charm!

Thanks both of you for answering!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial