+ Reply to Thread
Results 1 to 9 of 9

Finding values in a Range and then sorting Horiztonally (while removing blanks)

  1. 10-23-2013, 09:33 PM #1
    JasonNeedsHelp
    JasonNeedsHelp is offline
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Finding values in a Range and then sorting Horiztonally (while removing blanks)

    Hi All,

    The title kinda says it all...

    I have a range of cells (bordered) with some unique values that are scattered and want an array formula that will pick them out and sort then numberically and horizontally. It's easier to look at the example provided...

    Also in addition to how to do this can you show me in two steps how to do this so i can learn from it... The first step: how to index the range and find unique values... and the second: how to make the values go hoizontally instead of down a column...

    Thanks in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-24-2013, 12:44 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,011

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    1st of all, all your numbers are actually text, that just looks like a number. before you can do anything with them, you need to convert them to values.

    high light the entire range, and if you look at the top left corner of the 1st cell, you will see a yellow diamond - click that and select "convert to numbers"
    Now you can use the =small() function...
    =SMALL($B$2:$O$4,COLUMN(A1)) copied across
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 10-24-2013, 01:38 AM #3
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    Pl see the file with formulas dragged horizontally.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-24-2013, 09:16 AM #4
    JasonNeedsHelp
    JasonNeedsHelp is offline
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl see the file with formulas dragged horizontally.
    Thanks, there were two answers in here and the both formulas works for me! So posted here below for others to easily find.


    Please Login or Register  to view this content.
    The one from FDibbins just didn't work; I'm unsure as to why i couldn't get it working but every time i dragged it across i was getting error.

    Thanks both of you guys for the help! I think i now know how to do this for myself next time!
    Reply With QuoteRegister To Reply
  5. 10-24-2013, 11:13 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,011

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    Happy to help and thanks for the feedback

    The one from FDibbins just didn't work; I'm unsure as to why i couldn't get it working but every time i dragged it across i was getting error.
    Perhaps if you explained a bit more, we could figure out what went wrong for you?
    Reply With QuoteRegister To Reply
  6. 10-24-2013, 11:37 AM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    Quote Originally Posted by JasonNeedsHelp View Post
    Thanks, there were two answers in here and the both formulas works for me! So posted here below for others to easily find.


    Please Login or Register  to view this content.
    The one from FDibbins just didn't work; I'm unsure as to why i couldn't get it working but every time i dragged it across i was getting error.

    Thanks both of you guys for the help! I think i now know how to do this for myself next time!

    Actually, the formula FDibbins provided works just fine. It is the "numbers" on the spreadsheet which are text and need to be converted to real numbers. That's all.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  7. 10-24-2013, 11:40 AM #7
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    thanks for reply. Pl mark the thread solved.
    Reply With QuoteRegister To Reply
  8. 10-24-2013, 11:48 AM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,011

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    Thanks for the assist AlKey

    Im sure that after 1300 posts, you should know by now how to mark a thread solved?

    mark it SOLVED using the thread tools
    Reply With QuoteRegister To Reply
  9. 10-25-2013, 11:50 AM #9
    JasonNeedsHelp
    JasonNeedsHelp is offline
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Re: Finding values in a Range and then sorting Horiztonally (while removing blanks)

    Sorry, I'll remember for next time! Thanks for the reminder.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automatically and continuously removing blanks in data range
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-05-2012, 02:25 AM
  2. Finding 3 tables on a sheet and copying them, and then removing the blanks
    By the00 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-14-2012, 06:36 AM
  3. Retrieve unique values from a list.... while removing blanks.
    By Magsy in forum Excel General
    Replies: 3
    Last Post: 12-13-2011, 08:00 AM
  4. removing blanks from named range list
    By jik_ff in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2010, 03:54 PM
  5. Removing blanks / sorting - common methods not applicable
    By Dark_Templar in forum Excel General
    Replies: 1
    Last Post: 05-25-2006, 10:00 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1