+ Reply to Thread
Results 1 to 8 of 8

Formula to get a unique value list and sort one column by numbers in another column

  1. #1
    Registered User
    Join Date
    02-27-2007
    Posts
    38

    Formula to get a unique value list and sort one column by numbers in another column

    I have a sheet that has a couple of columns that contain some names, and counts associated with those names.

    Column A Column B
    Person Count
    A 1
    B 3
    A 4
    B 5
    C 2
    D 3
    D 1


    I'd like to create a formula that I can copy down a third column that will produce a list of unique values from the person column, sorted by the sum of the counts for that person from the counts column.

    I.e. the output from this formula would be a column looking like this
    Column C
    sorted unique list
    B
    A
    D
    C


    (where the sort order comes from the fact that the counts for B in the original two tables sums to 8, A to 5, D to 4, and C to 2).

    It seems like I should be able to create some array formula to do this, but I'm getting stuck when I try.

    Can anyone help?

    Thanks!
    Last edited by DaveF; 07-23-2010 at 02:21 PM.

  2. #2
    Forum Contributor
    Join Date
    05-16-2010
    Location
    UK
    MS-Off Ver
    2003 and 2007
    Posts
    252

    Re: Formula to get a unique value list and sort one column by numbers in another colu

    Would a pivot table be an option?

    Please see attached.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-27-2007
    Posts
    38

    Re: Formula to get a unique value list and sort one column by numbers in another colu

    No, I thought of that, but it needs to be something that does it on the fly, so I don't want to be required to refresh a pivot table as the information in the first two columns changes. Thanks though.

  4. #4
    Forum Contributor
    Join Date
    05-16-2010
    Location
    UK
    MS-Off Ver
    2003 and 2007
    Posts
    252

    Re: Formula to get a unique value list and sort one column by numbers in another colu

    Could you use a Worksheet Change event to refresh the pivot table when data is entered in columns A or B? Something like:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,003

    Re: Formula to get a unique value list and sort one column by numbers in another colu

    Add two help columns to solve it.
    See attached file.

    HTH,
    WindKnife
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Formula to get a unique value list and sort one column by numbers in another colu

    Also with a couple of helper columns.


    Regards.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-27-2007
    Posts
    38

    Re: Formula to get a unique value list and sort one column by numbers in another colu

    Thanks everyone.

  8. #8
    Forum Contributor
    Join Date
    05-16-2010
    Location
    UK
    MS-Off Ver
    2003 and 2007
    Posts
    252

    Re: Formula to get a unique value list and sort one column by numbers in another colu

    Without any helper columns. In C2 copied down:

    =IFERROR(INDEX($A$2:$A$32,MATCH(LARGE(INDEX((COUNTIF($C$1:C1,$A$2:$A$32)=0)*(ROW($A$2:$A$32)/10^10+SUMIF($A$2:$A$32,$A$2:$A$32,$B$2:$B$32)),0),1),INDEX(ROW($A$2:$A$32)/10^10+SUMIF($A$2:$A$32,$A$2:$A$32,$B$2:$B$32),0),0)),"")

    Please see attached.
    Attached Files Attached Files
    Last edited by pb71; 07-24-2010 at 08:34 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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