Array Formula - sort results alphabetically

[kenderweasel]

Hi,

I have an array formula, which returns a list of text codes - at the moment, these are not sorted alphabetically, however, I've now been asked to change this to return the codes in alphabetical order. Can anyone help?

=IFERROR(INDEX(Database!$B$1:$DD$1,1,SMALL(IF(((Database!$C$1:$DD$1000=$A$16)+(Database!$C$1:$DD$1000=$A$17)+(Database!$C$1:$DD$1000=$A$18))*(Database!A$1:A$1000=$H$7),COLUMN(Database!$C$1:$DD$1000)),ROW(A1))-(ROW(A$2)-1),1),"")

 

[Eric W]

Man, kenderweasel, you really drove me crazy!

I looked at your question, and my first thought was it's possible. Then as I deconstructed your formula, I decided it's definitely NOT possible! But it kept nagging at me all day, and I thought of some ways to get around the problem, only to have another one arise, then I'd figure that one out, and so on. So here's what I came up with:

	B	C	D	E
1	List	List	List	List
2	xx	xx	cc	cc
3	cc	cc	pp	pp
4	pp	pp	xx	xx
5

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula B2 {=IFERROR(INDEX(Database!$B$1:$I$1,1,SMALL(IF(((Database!$C$1:$I$10=$A$16)+(Database!$C$1:$I$10=$A$17)+(Database!$C$1:$I$10=$A$18))*(Database!A$1:A$10=$H$7),COLUMN(Database!$C$1:$I$10)),ROW(A1))-(ROW(A$2)-1),1),"")} C2 {=IFERROR(INDEX(Database!$C$1:$I$1,SMALL(IF(Database!A$1:A$10=$H$7,IF(ISNUMBER(MATCH(Database!$C$1:$I$10,$A$16:$A$18,0)),COLUMN(Database!$C$1:$I$10)-COLUMN(Database!$C$1)+1)),ROWS($C$2:$C2))),"")} D2 {=IFERROR(INDEX(Database!$B$1:$I$1,MATCH(0,MMULT(COLUMN(Database!$B$1:$I$1)^0,IF(IF(MMULT(TRANSPOSE(ROW(Database!$A$1:$A$10)),(Database!$A$1:$A$10=$H$7)*(COUNTIF($D$1:$D1,Database!$B$1:$I$1)=0)*((Database!$B$1:$I$10=$A$16)+(Database!$B$1:$I$10=$A$17)+(Database!$B$1:$I$10=$A$18))),Database!$B$1:$I$1,"a")>TRANSPOSE(IF(MMULT(TRANSPOSE(ROW(Database!$A$1:$A$10)),(Database!$A$1:$A$10=$H$7)*(COUNTIF($D$1:$D1,Database!$B$1:$I$1)=0)*((Database!$B$1:$I$10=$A$16)+(Database!$B$1:$I$10=$A$17)+(Database!$B$1:$I$10=$A$18))),Database!$B$1:$I$1,"a")),1,0))-SUM(IF(MMULT(TRANSPOSE(ROW(Database!$A$1:$A$10)),(Database!$A$1:$A$10=$H$7)*(COUNTIF($D$1:$D1,Database!$B$1:$I$1)=0)*((Database!$B$1:$I$10=$A$16)+(Database!$B$1:$I$10=$A$17)+(Database!$B$1:$I$10=$A$18)))=0,1)),0)),"")} E2 {=IFERROR(INDEX($B$2:$B$10,MATCH(0,IF($B$2:$B$10="",-1,COUNTIF($B$2:$B$10,"<"&$B$2:$B$10))-ROWS($E$2:$E2)+1-SUM(IF($B$2:$B$10="",1)),0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The first formula in B2 is your exact formula. The one in C2 does exactly the same thing, but a bit more concisely written. It helped to figure out exactly what yours was doing. Then the petite formula in D2 extracts the same values in sorted order. It's not extensively tested since I don't have your data to work with. Also note that I tested on a much smaller range than your original formula. If you want to try it, you'll have to change the ranges to match your sheet. HOWEVER, this formula is VERY computationally intensive, and I shudder to think about how it will slow down your sheet.

You'd probably be better off using your current formula in B2 as a helper column, then something like the formula in E2 to sort them.

So give me an interesting problem, and it'll nag at me until I solve it, but I spent a lot of time today working on a formula that will probably not be usable. If you give it a try, let me know how it works.

 

[kenderweasel]

Thank you so much for taking so much time to get a solution

I've ended up using E2 in combination with my original formula, as I couldn't get D2 to work - but it does do what I need, so it's great :D

Thanks
Anj

 

[Eric W]

That's probably the best idea. The D2 formula is proof that it can be done, but it's inefficient and probably needs tweaking to make it work with your sheet. But I am happy you got the E2 formula to work for you, that was almost an afterthought when I added that.

Glad to help!