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

1. ## 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...

2. ## 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

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

Pl see the file with formulas dragged horizontally.

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

Originally Posted by kvsrinivasamurthy
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!

5. ## 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?

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

Originally Posted by JasonNeedsHelp
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.

8. ## 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

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

Sorry, I'll remember for next time! Thanks for the reminder.

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

#### 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