+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Remove Duplicate Values from top 5

  1. #1
    Registered User
    Join Date
    07-31-2011
    Location
    Bhutan
    MS-Off Ver
    Excel 2007
    Posts
    16

    Unhappy Remove Duplicate Values from top 5

    Hello Everyone,
    I have gone through many of the threads but could not find the appropriate answers for my problem. My problem is as follows.
    I have 50 values in column A .eg. 30,40,40,50,20,25,30,35,45,15,etc
    I want to display the top 5 values in Column B. So i use the formula LARGE($A$1:$A$50,1) in B1, LARGE($A$1:$A$50,2) in B2, etc.The top 5 values given by the above formula are 50,45,40,40,35. BUT I don't want the same values (eg.40) to appear in the top 5 list. Instead, I want to show the next value (35) in place of 40 (2nd 40) and so on.
    Can any one help me to solve this issue of not showing duplicate values.
    Thanks for your kind help.

    Loknath

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Remove Duplicate Values from top 5

    Assuming data is in say A1:A10, try:

    Please Login or Register  to view this content.
    adjust ranges to suit and confirm formula with CTRL+SHIFT+ENTER not just ENTER and copy down.

    The ROWS($A$1:$A1) increases the k factor by one as you copy down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1