9 Responses to “Sorting numbers and text cells also removing blanks using array formula in excel”

1. Jon Says:
   August 26th, 2010 at 10:23 am
   
   

   Okay here's what I need to do:
   I have 5 lists each with somewhere between 10 to 30 text values associated with a title of that list. On another worksheet I have 2 dropdown lists with the 5 list names in each one. You may choose one of the names or two. I want another worksheet (based on those choices) to return all matching results and place them in a dropdown list, I need it to update if you change the choices at any time.

   Does anyone know how to do this? I've been able to figure out alot of things but this one I can't clearly find out how to accomplish. I can and have used VBA on this sheet to accomplish other things but I'm not proficient in writing the code from scratch as I'm still learning it.

2. baum schausberger Says:
   November 1st, 2011 at 2:08 pm
   
   

   I don't see in your page my problem. I have a matrix 5x100 I tried wiht data-sort..etc. I need to sort by row, nothing can be change by columnns, so data-sort change one row, when I tried the next, the first one come back to the original desorger, how can I change the whole matrix in one time by rows not by columns. example.
   9-8-7-6-5
   6-5-3-2-1
   98-87-54-21-32 etc.
   thank you for any help.

3. Oscar Says:
   November 2nd, 2011 at 3:13 pm
   
   

   baum schausberger,

   Excel 2007:
   1. Select cell range
   2. Right click on cell range
   3. Click Sort...
   4. Click Custom sort
   5. Click options...
   6. Select Orientation: Left to right
   7. Click OK
   8. Select a row
   9. Click OK

4. Brian Minton Says:
   February 6th, 2012 at 3:30 pm
   
   

   This doesn't work correctly with text starting with an equal sign:
   (Yes, I know it's a pathological case)

   1) Enter some normal data in the List range
   2) Enter a text string starting with a single quote and an equal sign (or, format a cell as Text and put a formula in it)
   3) notice duplicate entries in the output not present in the input

   List Sorted
   a 1
   aaa 2
   1 9
   2 10
   b 11
   777 777
   9 1E+88
   c a
   10 aaa
   11 aaa
   1.00E+88 b
   =A17+1 c
   e e
   g f
   f g

5. Brian Minton Says:
   February 6th, 2012 at 3:33 pm
   
   

   here's the same data, but easier to read:
   List
   a
   aaa
   1
   2
   b
   777
   9
   c
   10
   11
   1.00E+88
   =A17+1
   e
   g
   f

   Sorted
   1
   2
   9
   10
   11
   777
   1E+88
   a
   aaa
   aaa
   b
   c
   e
   f
   g

   Note the duplication of the aaa entry, and the non-inclusion of the text entry starting with =

6. Brian Minton Says:
   February 6th, 2012 at 3:35 pm
   
   

   Also, the formula failed to work with the entire text of the United States Declaration of Independence in a single input cell. It just puts #VALUE in every output

7. Brian Minton Says:
   February 6th, 2012 at 3:37 pm
   
   

   and for shorter multi-line inputs, it concatenates them:
   'ab'
   becomes
   'ab'

8. Brian Minton Says:
   February 6th, 2012 at 3:39 pm
   
   

   although 'ab' becomes 'ab', it's still sorted differently. The comment form isn't allowing me to correctly format what I'm trying to write.

9. Oscar Says:
   February 7th, 2012 at 1:51 pm
   
   

   Brian Minton,

   This is what I get:
   

Leave a Reply