SORTBY problems

[ollyhughes1982]

Hi,

I am attempting to SORTBY function, but don’t seem to be able to get it to work. Please could someone show me where I am going wrong.

The current formula I am using is:

=IF(A2="","",IFERROR(SORTBY(B2:B10001,'Sort List'!A2:A10001),""))

In column D of the ‘Unique Character Extraction’ worksheet, I am trying to sort (spilling down) the non-blank cells from the range B2:B10001t. I have set the order I would like them sorted by in A2:A69 of the second worksheet (‘Sort List’), but it doesn’t seem to be working. For some reason it is showing ‘8’ first ands then a seemingly random order after that, including 0 values. It should sort as I have set in the second worksheet. I assumed SORTBY was the correct function for this, but doesn’t seem to be working.

Any help much appreciated!

I’ve also added a link to the file: parkrun - Extract Unique Characters.xlsx

Thanks in advance!

Olly.

 

[ollyhughes1982]

I now have a found a way to do this, but I am now trying to remove the 0 results that I get. in have added IFERROR and an IF, but i am still getting 0 values in the remaining cells.

This is my formula:

=IF(B2="","",IFERROR(SORTBY(B2:B10001,MATCH(B2:B10001,'Sort List'!A2:A10001,0)),""))

 

[Cubist]

Maybe this.

=SORTBY(B2#,UNICODE(B2#))

 

[ollyhughes1982]

So

=IF(B2="","",IFERROR(SORTBY(B2#,UNICODE(B2#)),""))

?

This doesn't reference my custom list on the second sheet though?

Or like this?

=IF(B2="","",IFERROR(SORTBY(B2#,UNICODE(B2#)),MATCH(B2:B10001,'Sort List'!A2:A10001,0)),""))

 

[Cubist]

Your list on the Sort List tab isn't exactly custom. Isn't it just getting the ASCII code of the character?

 

[ollyhughes1982]

No, it's a different order to the standard sort. It's the way I need to do it. My solution has it sorting correctly, I just want to be able to get rid of the zero values at the bottom that go all of the way to the end of the range.

 

[Cubist]

I'm not following. I don't see how your column B on the Sort List is different than the standard sort?

 

[ollyhughes1982]

I’ve solved the sort order with my match function. I just need to amend it to remove the unwanted zero values

 

[Cubist]

Ok, did the TOCOL solve the zero value?

 

[ollyhughes1982]

Ok, did the TOCOL solve the zero value?

I can’t see TOCOL?