LinkBack URL
About LinkBacks
Is there a way to have the list that is being sent to output in alphabetical order with a formula (without using a filter)?
=IFERROR(INDEX(OSList, MATCH(0,IF(ISBLANK(OSList),1,COUNTIF($K$25:K25, OSList)), 0)),"")
Is there a way to have the list that is being sent to output in alphabetical order with a formula (without using a filter)?
=IFERROR(INDEX(OSList, MATCH(0,IF(ISBLANK(OSList),1,COUNTIF($K$25:K25, OSList)), 0)),"")
You should describe your sheet structure and the values that you have rather than giving a formula that does not work.
Bernie Deitrick
Excel MVP 2000-2010
Try this...
Data Range
This array formula** entered in B2:
=IFERROR(INDEX(A$2:A$10,MATCH(TRUE,COUNTIF(A$2:A$10,"<"&A$2:A$10)=SUM(COUNTIF(A$2:A$10,B$1:B1)),0)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony,
This formula works when creating a new blank spreadsheet but when applying it using it on my formula it comes up blank. I believe this is because the cells its pulling from (A$2:A$10) are not just static text, lines A$2 through A$10 are formulas as well.
Is there an easy way to fix this?
I tried creating another row and just hit =A$2 and down the row but it still comes up blank.
Thanks
Can you post a SMALL sample file so I can see what's happening?
The key word is SMALL. 20 rows worth of data is plenty.
This is hard to replicate unfortunately, bare with me.
I created a new document with the same coding, same fields, etc. and it works.
My original document has sensitive data but it setup the same and its failing, I'm trying to figure out why now.
Disregard, I got it working. The range (OSList) was the issue in my case.
My range (OSList) was A1:A2000, but my actual range with items in it was rows A1:A1230.
Tony, when I took your code and put the actual range in (A1:A1230) it worked. If I did (A1:A2000) or (A1:A1231 or more) it failed.
Excel just being picky.
Did some testing and discovered the formula doesn't handle formula blanks "".
I'm guessing that your range of data has formulas that return formula blanks at the bottom of the range.
If that's correct then you should be able to use a dynamic range if you'll be adding new data in the future.
Let me know.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks