counting occurances past the first

  • Thread starter Thread starter Thomas
  • Start date Start date
T

Thomas

heres my situation:
I have a range of numbers named "data". There largest number in this
range can be 50,the smallest 1.there can be more then one of any number
in the range.The count of numbers in the range is undermined.

To find the count of 1 to 50's in the range I use
COUNTIF(data,ROW(1:1))and drag down 50 rows.This range is called 'cnt'

To get the largest count of each number in the this range(cnt) i use
LARGE(Cnt,ROW(1:1))and drag down 50 rows

To get the original number counted back from the first formuala I use
=+MATCH(J2,Cnt,0)and drag down 50 rows.This works, however since the
second formula can return duplictes the match formula always returns the
first matched occurance,I need it it to find the first and then the
second,etc if applicable.

Hopefully you get the general idea of what I'm asking.



There in lies the problem
 
What entries are in J2/column J. What are you trying to show in the column
with the match formulas?
 
I'll rephrase the question
data is a range which holds 45000 + numbers ranging from 1 to 50
I need to get a list from 1 to 50 of the numbers in data starting with
the most frequent one and on down.Since some can appear the same number
of times. I need a way to get past the first duplicate count and pick
up the next.
Example 3 and 45 both appear 1200 times 5th in the range of 1 to 50
3 would be 5
and
45 would be 6
instead of 5 too ,in other words advance one on duplicates
 
Thomas said:
data is a range which holds 45000 + numbers ranging from 1 to 50
I need to get a list from 1 to 50 of the numbers in data starting with
the most frequent one and on down.Since some can appear the same number
of times. I need a way to get past the first duplicate count and pick
up the next.
Example 3 and 45 both appear 1200 times 5th in the range of 1 to 50
3 would be 5
and
45 would be 6
instead of 5 too ,in other words advance one on duplicates
Click to expand...

You could do this as follows. If the topmost result cell were F1 and the
data range were named Data, then the formulas would be

F1:
=INDEX(ROW(INDIRECT("1:50")),
MATCH(COUNTIF(Data,MODE(Data)),FREQUENCY(Data,ROW(INDIRECT("1:49"))),0))

F2: [array formula]
=INDEX(ROW(INDIRECT("1:50")),
MATCH(COUNTIF(Data,MODE(IF(COUNTIF(F$1:F1,Data)=0,Data))),
FREQUENCY(IF(COUNTIF(F$1:F1,Data)=0,Data),ROW(INDIRECT("1:49"))),0))

Select F2 and fill down into F3:F50. This is hugely redundant in terms of
recalculation, but it uses only 50 cells. If you're willing to use 50 extra
cells and some menu commands and can live with static results, enter =ROW()
in each cell in, say, E1:F50, then select E1:E50 and enter the array formula
=FREQUENCY(Data,D1:D49) [yes - D49, not D50]. Select E1:F50, Edit > Copy,
Edit > Paste Special as values on top of itself, then sort E1:F50 first by
column F in descending order then by column E in ascending order.
 
Back
Top