Useing the Large Function or some other variable

[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

 

[Dave Peterson]

Maybe you could use data|filter|advanced filter and check that unique records
only.

Debra Dalgleish has some instructions with pictures:
http://www.contextures.com/xladvfilter01.html#FilterUR

 

[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

 

[Ardus Petus]

Then you'll need some VBA macro code. Do you mind?

--
AP

"chuck1968" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[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

 

[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

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

 

[Pete_UK]

Sorry, I wrongly attributed the formula to Tom - it was Aladin Akyurek.

Pete