Help with sorting on 4 columns

When you concatenate numbers, they are converted to text and sorted as text. I would multiply each value by an appropriate weight and then sum the values.

If the numbers are integers, you can also format them with an appropriate number of leading zeroes:

format(Value1,"000000000") + format(Value2,"000000000") + format(Value3,"000000000") + format(Value4,"000000000")

The problem with using the format() function is the same as with the text() or concat() functions. Sorting will be based on the numbers' ASCII values and not their numeric values.

When numbers formatted as text are sorted, the sorting is indeed performed on the numerical values of the ASCII (or Unicode) codes of the characters representing the digits. Is it a problem? It depends... First, the numerical values of the ASCII codes of the characters representing the digits happen to be ordered in the same way as the numerical values of the digits themselves. So far so good. The problem arises when sorting numbers whose length, when represented as strings showing their decimal value, is different. The essence of the problem is that a string comparison is performed by aligning the strings on the left, and then proceeding from left to right. So, if the number 2 is represented by the string "2", and the number 10 by the string "10", performing a string comparison, left aligned, first compares "2" with "1", and concludes that "2" is greater than "10". Now, if one formats the numbers with leading zeroes, the number representations as strings become, for example with the format "000", "002" and "010". One can easily verify that the digits are now correctly aligned, and that a string comparison gives the correct result.

The two techniques presented in this thread, by multiplication or by formatting, work equally well for a small number of small numbers. If many numbers, where each one can take a large value, are involved, the multipliers can rapidly need to take large values. It has been recently demonstrated by Jacques Tur that the arithmetic implementation in Ninox can only handle about 15 decimal places, which sets a limit on the usable multipliers. With the formatting technique, the limit is the size of the string that can be handled.

I'll keep that in mind the next time I do a 16+ column sort.