Useing the Large Function or some other variable

C

chuck1968

I'm making a spread sheet and I have a list with different numbers with
some duplicates.

I want to get a list of each number. I'm using the LARGE Function, but
it still list the duplicated numbers, I do not want the duplicate
numbers to show up. Can anyone help?

chuck1968 :)
 
C

chuck1968

That worked, but my list of numbers will change continously.
I don't want have to do this everytime if possible.

Thanks for your reply.

Chuck:)
 
P

Pete_UK

You might like to try this formula method, based on a posting the other
day by (I think) Tom Ogilvy:

Assume your numbers are in A2 to A100 with headings on the top row. In
B2 you can add this formula:

=MODE(A2,A100)

and in B3:

=MODE(IF(ISNUMBER(MATCH($A$2:$A$100,$B$2:B2,0)),"",$A$2:$A$100))

As this is an array formula, you do not press ENTER when you have typed
it in - instead you have to do CTRL-SHIFT-ENTER at the same time. If
you do it correctly then Excel will wrap curly braces { } around the
formula - you do not type these yourself.

You can then copy this into B4 onwards, until it starts to return
errors. This will give you the most frequent numbers down column B. If
you want to know how many there are of each, then you could add this
formula to C2:

=COUNTIF(A$2:A$100,B2)

and copy this down. Obviously, adjust the ranges to suit your data.

Hope this helps.

Pete
 
R

Ron Rosenfeld

I'm making a spread sheet and I have a list with different numbers with
some duplicates.

I want to get a list of each number. I'm using the LARGE Function, but
it still list the duplicated numbers, I do not want the duplicate
numbers to show up. Can anyone help?

chuck1968 :)
Click to expand...

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then enter this formula in some cell:

=INDEX(UNIQUEVALUES(rng),ROWS($1:1))

Copy/drag down as far as needed.

rng is your list

This will adjust dynamically as you change your list of numbers.


--ron
 
Top