Tough one for me

  • Thread starter Thread starter Randy S
  • Start date Start date
R

Randy S

I have a 2-dimensional array. I first want to see what is
the most frequently occuring item in the first column. I
also want to see what item has the largest sum in the
second column. Here's an example:

Col A Col B
===== =====
X $25
Y $32
X $11
Z $29
P $18
X $17
Y $64
etc. etc.

Question 1: In this case, I would get X as the most
frequenty occurring (3x), Y as the second (2x), etc.
Question 2: In this case, I would get Y as the highest sum
($96), X as the second ($53), etc.

Keep in mind that this list is in reality quite long -
20,000 items. Also this needs to be dynamically computed
in a formula so it can be used in other formulas; pivot
tables will not work.
 
Randy,

A pivot table can be made to refresh any time a change is made to the
underlying data via an event (worksheet_change) macro. However, if the data
in the table is linked to other sheets (formulas that refer to another
sheet), it won't fire on such changes from the other sheets. The pivot
table will provide the counts and sums you need for any items that occur in
col A.

Though you could certainly call this a 2-dimensional array, it's really a
table.
 
Back
Top