Sorting with functions

[zhavier]

I have an issue where I am trying to sort a large list, but it results in a huge processing delay. I am open to using helper columns, but I can't figure out how to get them to work. Attached is an example of the size and functions I tried, using an array function.

[nathansav]

Turning the calculation off before sorting may help.

[ben_hensel]

I would split Bldg/Floor/Area into their own separate columns and then use Sort/Filter on that. If the cell contents are all the exact same format like that then you could do it pretty easily with LEFT and RIGHT functions.

...Putting information like that in individual records (rather than three-to-a-cell) would be better database design, so it will help you out in all sorts of directions.

[zhavier]

1) I do actually have them in separate columns, but it was easier to do the sort array function with them all in one column. Also Area can be more complicated than just A, it might be a whole phrase, like Security Office, Public Area, Stairwell, etc. I can't use Sort/Filter because I need it to be done each time a new entry is created.

I also don't actually need the sorted list, I need the row numbers of the original locations of the sorted list, as shown in the example excel.

[zhavier]

So, I appear to have solved my own issue.

Countif(Range, "<="$B2) will tell me how the current item ranks in the list. Then I just have to handle the equal values.

Attached is an example of how I accomplished my goal. It probably doesnt need as many helper columns but it was easier to keep track of what was going on.