• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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

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!
  • 2
1 Solution
barry houdiniCommented:
You can use this array formula in H4


confirmed with CTRL+SHIFT+ENTER

...and this one in H5


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
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.
Closer to a solution - the pivot table now has rank

How to add rank to Pivot Table

New file with rank attached
r_johnstonAuthor Commented:
This worked like a charm!

Thanks both of you for answering!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now