+ Reply to Thread
Results 1 to 4 of 4

Function to sort text values in one column into another column

  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Montara, California, US
    MS-Off Ver
    Excel 2007
    Posts
    16

    Function to sort text values in one column into another column

    I have a column where each row has text or is blank. I need a function that can sort those values into a 2nd column in ascending order of text. I thought Small(array,k) would work but it doesn’t for text. And I can’t find any function or combination of functions to do that. I need to do this without a user having to invoke the Excel sort.

    So if a column has

    ZZ
    AA
    blank
    FF
    DD
    blank
    blank
    CC

    I would want to end up with
    blank
    blank
    blank
    AA
    CC
    DD
    FF
    ZZ

    I have a combination of functions that will let me pull just the text from that column into a 3rd column to end up with only value sorted text

    Thanks for any help/suggestions

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Function to sort text values in one column into another column

    I did it with helper columns, but I'm sure there's a way to truncate the steps:

    Assuming your data runs A2:A9:

    B2: =SUMPRODUCT((A2>$A$2:$A$9)+1)
    C2: =RANK(B2,$B$2:$B$9,1)+COUNTIF($B$2:B2,B2)-1
    E2: =ROW(A1)
    F2: =IF(INDEX($A$2:$A$9,MATCH(E2,$C$2:$C$9,0))=0,"",INDEX($A$2:$A$9,MATCH(E2,$C$2:$C$9,0)))

    and copy all 4 formulas downwards

    sorting without sort.xlsx
    Last edited by daffodil11; 12-19-2013 at 04:55 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    04-16-2013
    Location
    Montara, California, US
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Function to sort text values in one column into another column

    Thank you, that combination of functions does the trick. Didn't realize one could use sumproduct for that. Very helpfull.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Function to sort text values in one column into another column

    Glad I could help out. SUMPRODUCT is a real gem!

+ 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. [SOLVED] sort and delete based on the values in column c and column j?
    By barbibchn in forum Excel General
    Replies: 1
    Last Post: 01-14-2013, 04:25 PM
  2. [SOLVED] Count multiple values of same text down a column - sort/highlight
    By elapo21 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-20-2012, 03:15 PM
  3. Sort through a column and for every X, add values from next column
    By MikeFike in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2011, 12:11 AM
  4. Sort one column based on values in another column?
    By mogabi in forum Excel General
    Replies: 2
    Last Post: 01-17-2011, 01:26 AM
  5. Sort Column C for all similar values in Column A
    By R_S_6 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-15-2009, 08:02 AM

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