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

2 comments:

  1. hi!
    thanks for the help, but it doesn't seem to work for me.. would it be easier to use a mode function based on the content of the two columns?

    or with it be better to use a VBA code?

    thanks for the help again...

    ReplyDelete
  2. Hi Anne:

    Please explain how it doesn't work for you? ..,
    1) whether I have misunderstood your question, or
    2) the results are incorrect
    I have incorporated two solutions now ... Sheet1 with a twoColumnOutput and Sheet2 with a single column output.
    Please check it out and let me know how it goes for you.

    Cheers!
    Yogi

    ReplyDelete