Tracking High Frequency Occurences

[Landon Cornett]

I am trying to track the most frequently occurring statements on a sheet.
For example:

Column A Column B
People Apples
People People
Places People
Apples People
Oranges Places
Bananas Places

I need to track the top 3 occurences, so in this circumstance, I would want
the output at the bottom of the screen to read:

Trends:
People
Places
Apples

Any help would be appreciated.

 

[T. Valko]

What makes this difficult is that your data is in multiple columns. Are
there any empty/blank cells within the range? Are both lists of data the
same length?

 

[Landon Cornett]

The lists of data are the same length, and there are actually 5 columns of
data, one for each day of the work week, and there are no blank cells within
the range.

 

[T. Valko]

Ok, this is not easy!

You basically have 2 formula options. One requires that you create a one
column array of your data. The other requires that you download and install
the *free* addin Morefunc.xll from:

http://xcell05.free.fr/english/

Included in this addin is a function that'll turn multi-dimensional ranges
into a one dimensional array. Then you can use array** formulas like this:

For the most frequently occurring item:

Assuming your range of data is given a defined name of rng.

=INDEX(ARRAY.JOIN(rng),MODE(MATCH(ARRAY.JOIN(rng),ARRAY.JOIN(rng),0)+{0,0}))

Assume the above formula is entered into cell F2. Then enter this formula in
F3 and copy down to F4:

=INDEX(ARRAY.JOIN(rng),MODE(IF(COUNTIF(F$2:F2,ARRAY.JOIN(rng))=0,MATCH(ARRAY.JOIN(rng),ARRAY.JOIN(rng),0)+{0,0})))

You can make the formulas shorter by giving this portion a defined name
since it's repeated often in the formulas:

ARRAY.JOIN(rng)

Suppose we name that Array then the formulas become:

=INDEX(Array,MODE(MATCH(Array,Array,0)+{0,0}))

=INDEX(Array,MODE(IF(COUNTIF(F$2:F2,Array)=0,MATCH(Array,Array,0)+{0,0})))

*All* of the above formulas are array formulas. ** Array formulas need to be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

 

[Landon Cornett]

Thank you so much for your reply, I am not going to be able to try it out
tonight, since it is late and I am tired, but I will post again tomorrow and
let you know if it works for me.

Thank you for working with me on this, you have greatly helped both me and
my coworkers and saved us tons of time.