A formula that creates an alphabetically ordered list using unique values from a range

I've been using Google Sheets for while and had a code that does this. Decided I wanted to move over due to issues with speed.

Heres that code I used : =unique(sort(TRANSPOSE(filter('Champions Played Sheet'!$C$4:$AV,'Champions Played Sheet'!$B$4:$B=C3)),1,true))

This sorted my values from a seperate sheet, transposed them from a column, listing them into a row while extracting the unique values from it.

Now I have seen multiple videos on how to try and do it but it never seems to be functional for me in which ever method I try.

Heres an example of a formula I tried:

=INDEX('Champions Played Sheet'!C5:AR5,MATCH(MIN(IF(COUNTIF($B$1:B1,'Champions Played Sheet'!C5:AR5)=0,1,MAX((COUNTIF('Champions Played Sheet'!C5:AR5,"<"&'Champions Played Sheet'!C5:AR5)+1)*2))*(COUNTIF('Champions Played Sheet'!C5:AR5,"<"&'Champions Played Sheet'!C5:AR5)+1)),COUNTIF('Champions Played Sheet'!C5:AR5,"<"&'Champions Played Sheet'!C5:AR5)+1,0))

This came up with #NA where im trying to list words and not doing what I want. I dont know if this is due to the videos being based on earlier version of the software or im doing something wrong.

To mention, I am using a range from a column, not a row, and im getting the values from a seperate sheet (you guys can probably tell).

Can someone send me the right away in terms of videos or advice, or demonstrate what im doing wrong so I can learn? Pretty much a beginner here


Thanks

That formula needs:

1) to have all the C5:AR5 changed to $C$5:$AR$5

2) to be Array-Entered using Ctrl-Shift-Enter

3) to be in cell B1, and then copied down.

4) to not have any blanks in C5:AR5


This version needs to be array-entered in cell A2, and copied across (to transpose the unique values starting in C5 down to C45) :

=IFERROR(INDEX('Other sheet name'!$C$5:$C$45,MATCH(MIN(IF(COUNTIF($A$2:A2,'Other sheet name'!$C$5:$C$45)=0,1,MAX((COUNTIF('Other sheet name'!$C$5:$C$45,"<"&'Other sheet name'!$C$5:$C$45)+1)*2))*(COUNTIF('Other sheet name'!$C$5:$C$45,"<"&'Other sheet name'!$C$5:$C$45)+1)),COUNTIF('Other sheet name'!$C$5:$C$45,"<"&'Other sheet name'!$C$5:$C$45)+1,0)),"")


It does not reliably return the values in alphabetical order - not sure why.

Hi sorry for the late reply, have not been at the computer for a while due to issues that dont need to be shared on here.

I've tried doing what you said but all i get is the first value in that range of cells im taking it from. Heres the excel formula i created from your guidance.

=IFERROR(INDEX('Champions Played Sheet'!$C$5:$AR$5,MATCH(MIN(IF(COUNTIF(Sheet1!$A$3:A3,'Champions Played Sheet'!$C$5)=0,1,MAX((COUNTIF('Champions Played Sheet'!$C$5:$AR$5,"<"&'Champions Played Sheet'!$C$5:$AR$5)+1)*2))*(COUNTIF('Champions Played Sheet'!$C$5:$AR$5,"<"&'Champions Played Sheet'!$C$5:$AR$5)+1)),COUNTIF('Champions Played Sheet'!$C$5:$AR$5,"<"&'Champions Played Sheet'!$C$5:$AR$5)+1,0)),"")

I array entered it in so its a little bizarre. The only difference i can see is that im getting my range from list in a row, not a list in a column. I'm unsure really of where i need to go. The only part im a little confused with is the part i have bolded on the formula. Does this need to be the same as cell im putting the formula in or the cell next to it.

At this point, im not too bothered if it's alphabetical. Im just a sucker for a little bit of organization

Thanks again

You had posted - "transposed them from a column, listing them into a row"

So that was how I wrote the formula.

Now you say "my range from list in a row"

So, is your original data in a row, or a column, and do you want it in a row or a column?

I cannot find a stable single-column function-only solution that will transpose and sort consistently.

With a user-defined function in VBA, though, this is stable:

=Row2ColUA('Champions Played Sheet'!$C$5:$AR$5,ROW(A1))

copied down.

Place this code into a standard module and save the file as a macro-enabled .xlsm

Please Login or Register  to view this content.

Sorry - I forgot to add that the code requires a reference to MS scripting Runtime. In your Visual Basic Editor, choose Tools / References.. and then scroll down to find that file and put a check mark next to it:


Capture.JPG