+ Reply to Thread
Results 1 to 3 of 3

Formula for unique sort

  1. #1
    Registered User
    Join Date
    09-06-2018
    Location
    NC
    MS-Off Ver
    2010
    Posts
    2

    Formula for unique sort

    I'm having some trouble coming up with a formula to yield a unique 'sorting code.'

    I have a spreadsheet with client names in column A, and codes in column B. The codes in column B are meant to be sorted in ascending order (101, 201, 301, etc.). These codes may include duplicates. I want to write a formula to assign a 'sorting code' for each client based on ascending codes in column B--keeping all duplicates (see below).

    I'm at a loss on this one. Any suggestions?


    Client A 101 101-1
    Client A 101 101-2
    Client A 201 201-1
    Client A 301 301-1
    Client B 101 101-1
    Client B 201 201-1
    Client B 301 301-1
    Client B 301 301-2
    Client B 301 301-3

  2. #2
    Registered User
    Join Date
    09-06-2018
    Location
    NC
    MS-Off Ver
    2010
    Posts
    2

    Re: Formula for unique sort

    I just figured it out one possible option... =CONCATENATE(B2,"+",COUNTIFS($A$1:A2,A2,$B$1:B2,B2))

    It would be nice to not rely on the sort order for the formula to work. Still open to better suggestions that would work with a random sort order.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,650

    Re: Formula for unique sort

    sound like C1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy down.

    Or if you have a header in row 1 then in C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Edit:
    Whoops, I had this thread open for a while, and was working on something else meanwhile. So when I finished answering now, I can see you got the same idea as mine :-)
    Unfortunately it does not answer the "unsorted input data" part.

    Or it does?
    The counting goes on both column A an B, so ...
    Last edited by Kaper; 09-06-2018 at 12:24 PM.
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Non Array Formula to pull unique values and sort in a range
    By bjnockle in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-04-2021, 06:15 AM
  2. Data Validation List - Formula to Sort and Return Unique Values in List
    By beewketu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2015, 04:18 PM
  3. [SOLVED] Dynamic Formula for Getting Unique Items from a list and sort them alphabetically
    By tuckertheguy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-18-2015, 03:57 AM
  4. Replies: 5
    Last Post: 12-05-2014, 12:24 PM
  5. [SOLVED] Array formula to extract and sort unique values from two worksheets
    By rshukla in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2013, 06:11 AM
  6. [SOLVED] Formula to extract unique values from two excel tabs and sort alphabetically
    By rshukla in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-06-2013, 01:48 PM
  7. Replies: 7
    Last Post: 07-24-2010, 08:07 PM

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