Friday, May 27, 2011

yogi_Extract The Row(s) That Occur Most Often Based On Data In Columns A And B

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
crook_ed said:
I have to columns of data, Column A and column B. I want to count the data in column B based on the data in column A. Once i have the count, i have to produce row which appears the most.
For example, Column A consist of Items codes, and Column B consist of company names.
Item codes Company Names
1001 A
1001 A
1001 A
1001 B
1001 B
1002 A
1002 A
1002 B
1002 B
1002 B
My result should be the same as shown below, because both pairs appear the most number of times.
1001 A
1002 B
-----------------------------------------------------------------------------------
In Sheet1 the solution outputs the result in two columns with a geralized solution using ranges A:A, and B:B
and
in Sheet2 the solution outputs the result in one column using ranges A2:A11, and B2:B11