Forum Discussion
Sort(a:a) changes my 120 spreadsheet size to 1M+ filled with zeroes
It's filled with zeroes???
3 Replies
Interesting. I didn't know it would do that, but it is the correct answer to the problem as posed. You requested the formula should sort precisely 1,048,576 cells and it did. Older formulas allowed one to use entire columns and the calculation would get cut off when the boundary of the used range is reached, but that does not apply to array formulas.
I would recommend converting source data to Excel Tables and using the structured references to address the data. That way you will have one output for each place within the referenced column.
- Essentially, SORT is performing an EXPAND (albeit, involuntarily). It's interesting because Excel can perform this operation quickly. According to this guide, SORT uses approximate matching, so it makes sense.
https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions#speed-up-lookupsThanks Patrick. I added some logic to figure out the max size of my sort.
