Identify top 10 largest (unique) values in a table

[DonEB]

I have a table that spans the following range: CE30:CT45

This table will consist of many blanks but will mostly contain values ranging from 0 to 1. Yes, most of the values will be in decimal form (i.e., 0.25, .3333, 0.5, 0.6667, etc.) and yes... there will likely be many duplicates.

I need to come up with a list of unique values.

In column CC, starting in row 47 and going down, I've attempted to use =LARGE($CE$30:$CT$45,1), =LARGE($CE$30:$CT$45,2), =LARGE($CE$30:$CT$45,3), etc. to identify these values but realize that it will require inputting this formula into 320 cells to simply get me part way to where I need to go.

So, I have two questions:

1. Before I go to far with my original thought, I was wonder if there is something much more compact and efficient that someone could share that would help me achieve my goal.
2. If my initial thought was the best or only way to go then my next question would be... how do I examine the list of 320 cells (CC47:cc366) to come up with my unique list of numbers?

Thanks for any assistance that can be provided.

Don

 

[Logit]

.
Here is one way :




[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

1​

[/td][td]

6​

[/td][td]

88​

[/td][td]

10​

[/td][td][/td][td]

1st​

[/td][td]

99​

[/td][td]<-- =LARGE(A1:D10,1)[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

55​

[/td][td]

4​

[/td][td]

35​

[/td][td]

45​

[/td][td][/td][td]

2nd​

[/td][td]

88​

[/td][td]<-- =LARGE(A1:D10,2)[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

23​

[/td][td]

12​

[/td][td]

64​

[/td][td]

22​

[/td][td][/td][td]

3rd​

[/td][td]

82​

[/td][td]etc[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

42​

[/td][td]

82​

[/td][td]

27​

[/td][td]

34​

[/td][td][/td][td]

4th​

[/td][td]

72​

[/td][td]etc[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

6​

[/td][td]

32​

[/td][td]

24​

[/td][td]

38​

[/td][td][/td][td]

5th​

[/td][td]

65​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

58​

[/td][td]

15​

[/td][td]

99​

[/td][td]

52​

[/td][td][/td][td]

6th​

[/td][td]

64​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

12​

[/td][td]

14​

[/td][td]

36​

[/td][td]

13​

[/td][td][/td][td]

7th​

[/td][td]

58​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

32​

[/td][td]

65​

[/td][td]

41​

[/td][td]

15​

[/td][td][/td][td]

8th​

[/td][td]

55​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

28​

[/td][td]

43​

[/td][td]

43​

[/td][td]

18​

[/td][td][/td][td]

9th​

[/td][td]

52​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

39​

[/td][td]

72​

[/td][td]

21​

[/td][td]

19​

[/td][td][/td][td]

10th​

[/td][td]

45​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Aladin Akyurek]

@DonEB

In CC47 just enter:

=MIN(10,SUM(IF(FREQUENCY(CE30:CT45,CE30:CT45),1)))

In CC48 just enter: u-list

In CC49 control+shift+enter, not just enter, and copy down:

=IF(ROWS($CC$49:CC49)>$CC$47,"",MIN(IF(ISNA(MATCH($CE$30:$CT$45,$CC$48:CC48,0)),IF(ISNUMBER($CE$30:$CT$45),$CE$30:$CT$45))))

 

[DonEB]

Thank you Aladin. This works perfectly. Gave your solution a "Thumbs Up". If there is another way for me to indicate "problem solved", please let me know.

 

[Aladin Akyurek]

Thank you Aladin. This works perfectly. Gave your solution a "Thumbs Up". If there is another way for me to indicate "problem solved", please let me know.

You are welcome.

 

[DonEB]

@DonEB

In CC47 just enter:

=MIN(10,SUM(IF(FREQUENCY(CE30:CT45,CE30:CT45),1)))

In CC48 just enter: u-list

In CC49 control+shift+enter, not just enter, and copy down:

=IF(ROWS($CC$49:CC49)>$CC$47,"",MIN(IF(ISNA(MATCH($CE$30:$CT$45,$CC$48:CC48,0)),IF(ISNUMBER($CE$30:$CT$45),$CE$30:$CT$45))))

Aladin... almost two years ago you provided me with the code above. It has worked perfectly. Recently, I tried to duplicate that in a new spreadsheet but failed. Let me explain how.

First, here is the code I used:
In cell C40, I entered the following code: =MIN(10,SUM(IF(FREQUENCY(E5:X24,E5:X24),1)))
Then I highlighted C42:C51 and entered =IF(ROWS($C$42:C42)>$C$40,"",MIN(IF(ISNA(MATCH($E$5:$X$24,$C$41:C41,0)),IF(ISNUMBER($E$5:$X$24),$E$5:$X$24))))

I then proceeded to hold Control+shift and hit enter. Cell C40 contained the number 3 indicating there were only 3 unique values thus far... which was true. I then expected C42 to contain a 0, C43 to contain a 0.5 and C44 to contain a 1 but they were all zero's and then blank after that.

I can see that it turned into an array because it has the {} around each of the formula's within each of the 10 cells ranging from C42:C51.
But the cell references within the formula which were NOT absolute did not incrementally increase as I went down each cell to examine the formulas.

I'm a little perplexed as to what I may have done wrong. Any suggestions?

 

[Aladin Akyurek]

In C42 control+shift+enter:

=IF(ROWS($C$42:C42)>$C$40,"",MIN(IF(ISNA(MATCH($E$5:$X$24,$C$41:C41,0)),IF(ISNUMBER($E$5:$X$24),$E$5:$X$24))))​

then copy down.

Note that the formula is meant to pick out distinct numbers from E5:X24, that is, it expects numeric input in E5:X24.