Forum Discussion
Alphabetise help
Everyone has been so helpful on this forum; there are really some true experts. I’m making tracks with Excel but have really hit a stumbling block which seems like it should be an easy fix but I just can’t work it out.
So I’m taking data from Cells B2, O64 on sheet named ‘Remove 0s’
I’m transpose and alphabetising this information into Cells B2, 064 on sheet named ‘Alphabetised’
I’m using the formula
=SORTBY(‘Remove 0s’!B3:O64, ‘Remove 0s’!B3:B64, 1)
I need to cover that range of cells as more data may appear in the other rows up to row 64. As you can see from the picture, it is in ascending order however it has prioritised all of the blank cells first. I need those at the bottom.
Please could someone be so kind as to help me out. It’s hugely appreciated.
Thanks
Graeme
The SPILL error occurs because you use the formula inside a structured table, and that won't work. Such tables can't have dynamic array functions (i.e. ones that are spilling their results into multiple cells at once).
Firstly, I agree with Riny_van_Eekelen that it is better to have all input data prepared using Excel Tables, preferably without blank rows. The following formula both sorts and filters:
= SORT(FILTER(Table1, ISTEXT(Table1[Column1])))I would start by using a structured Excel table. Then you don't need to worry about the extra rows that may occur. When you add extra rows, the table will automatically expand and the SORTBY formula will pick-up the expanded table.
Riny_van_Eekelen Thank you so much for your response. Great idea. I’ve tried it but I’m just getting #SPILL
Ive linked the table which is called responses but it won’t have it
The SPILL error occurs because you use the formula inside a structured table, and that won't work. Such tables can't have dynamic array functions (i.e. ones that are spilling their results into multiple cells at once).
- ms query:
select * from [Remove 0s$a2:o] order by `first name`
