Solved

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

Posted on 2012-04-05
4
311 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
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

914 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now