Sorting an Array Formula

Hello everyone,

I have an array formula that works great and gives me the resulting list that I need. However, I would like to sort this list alphabetically so that the users who view the report do not have to search for their name (I could very easily filter the list so they could select their name - but even this is very problematic for them). The formula I currently have is:

{=IFERROR(IFERROR(IFERROR(IFERROR(INDEX('BF Beginning'!$W$2:$W$500,MATCH(ROWS(A$1:$A1)-1,COUNTIF($A$1:A1,'BF Beginning'!$W$2:$W$500)+('BF Beginning'!$W$2:$W$500=""),0)),INDEX(New!$X$2:$X$500,MATCH(0,COUNTIF($A$1:A1,New!$X$2:$X$500)+(New!$X$2:$X$500=""),0))),INDEX('Closed or Transferred'!$Z$2:$Z$483,MATCH(0,COUNTIF($A$1:A1,'Closed or Transferred'!$Z$2:$Z$483)+('Closed or Transferred'!$Z$2:$Z$483=""),0))),INDEX('BF End'!$W$2:$W$498,MATCH(0,COUNTIF($A$1:A1,'BF End'!$W$2:$W$498)+('BF End'!$W$2:$W$498=""),0))),"")}

I saw a formula in my search efforts that had a different "Match" block but I can't get it to work at the moment. The new formula was changed to:

{=IFERROR(IFERROR(IFERROR(IFERROR(INDEX('BF Beginning'!$W$2:$W$500,MATCH(ROWS($A$1:A1)-1,COUNTIF($A$1:A1,'BF Beginning'!$W$2:$W$500)+('BF Beginning'!$W$2:$W$500=""),0)),INDEX(New!$X$2:$X$500,MATCH(ROWS($A$1:A1)-1,COUNTIF($A$1:A1,New!$X$2:$X$500)+(New!$X$2:$X$500=""),0))),INDEX('Closed or Transferred'!$Z$2:$Z$500,MATCH(ROWS($A$1:A1)-1,COUNTIF($A$1:A1,'Closed or Transferred'!$Z$2:$Z$500)+('Closed or Transferred'!$Z$2:$Z$500=""),0))),INDEX('BF End'!$W$2:$W$500,MATCH(ROWS($A$1:A1)-1,COUNTIF($A$1:A1,'BF End'!$W$2:$W$500)+('BF End'!$W$2:$W$500=""),0))),"")}

Any suggestions?

Thanks!

Any suggestions?

it'd be great if we had a chance to look at those formulas in your file

Thank you Tim and Pepe! I've followed your directions and attached a sample of what I am trying to accomplish (Private data all over the place so the attachment is an extremely stripped down version).

Hello Glenn,

I am perfectly ok with helper column/tables that can be hidden.

Thanks,

Originally Posted by Glenn Kennedy

If I'm reading this correctly, you want an alphabetised, unique list from four separate columns (one per sheet). AFAIK, you can't do that without a helper column/Table, which can (of course) be hidden on another sheet. Is that OK?

Thanks for the suggestion Richard! Although I 100% agree with you I cannot do the slicer option at the moment (I've tried...multiple times on various reports...I'm only a small fish in a large pond that resists change). I've never used Power Pivot but will try and explore that option this afternoon.

Thanks so much Glenn!! This was perfect. I noticed that when I substituted the actual data back in it was doing something funky with order count. I changed your sort column slightly and it worked perfectly with the data!

The change I made was adding "=":

=IF(B2="","",COUNTIF(Uniques,"<="&B2))

Thanks again!!