Thursday, June 14, 2012

yogi_Generate A List Of Top Combination Of Locations And People In Descending Order

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #591   Jun 14, 2012     www.energyefficientbuild.com.

user kevinhtre said:
I have a set of data that lists dates, people, and locations (of which there are a finite number). I am attempting to produce from that a list of the "top X" for each location. So for instance:
1/17/12     John     New York
2/13/12     Bob      New York
3/1/12       John     Dallas
5/1/12       John     New York
6/1/12       Bob      Dallas
I am trying to produce from that:
New York: John, Bob   [ed. note: John first cuz he appeared twice above]
Dallas: Bob, John [or whatever, they both appeared once so it's a tie]
etc
I'm having a devilish time.
Thanks for any help.
----
I'm able to use a FREQUENCY to create a two-column list of pairs like so:
=ARRAYFORMULA(FREQUENCY(MATCH(A1:A14,UNIQUE(A1:A14),0), MATCH(UNIQUE(A1:A14),UNIQUE(A1:A14),0))) 
but it really leaves me with individual columns, and not pairs, which is difficult.
----------------------------------------------------------------------------------------------
following is a solution to the problem


1 comment:

  1. Very awesome, thanks. Any insight as to how I could sort the list so the higher frequencies are at the top?

    Like:

    Kevin:4
    Bob:3
    Dennis:2
    Other:1
    Other:1
    Other:1

    ReplyDelete