Column sort question

[clay5000]

Is there any way to determine whether a column is sorted? Like a property of a column like ".ascending" true or false?

 

[Andrew Fergus]

Hi

One way would be to use a formula to test if the data is sorted or not. Try the following array formula in an adjacent cell, and change the range to suit:

=IF(SUM(--(A1:A9>A2:A10))=0,"Sorted","Not Sorted")

After you enter the formula, but before you press enter, press the Ctrl key & the Shift key and the Enter key at the same time to convert this into an array formula. You will know if you have done this correctly when curly brackets {} automatically appear around the formula.

If you change the range of the formula, make sure the first range range starts and finishes one cell short of the second range : note I have used the ranges A1:A9 and A2:A10.

Andrew

 

[clay5000]

this works great for numeric data. Unfortunately, my list is alpha. It is employees and their customers.

col A col b

emp1 customer 1
emp1 customer 4
emp2 customer 2
emp2 customer 4

what happens is that I run a BeforePrint macro to insert page break between employees (col A) so to hand each one their page. But sometimes the employees manually sort on col b to get a list of customers, and then try to print. I would like to have my BeforePrint macro only insert the employee page breaks if col A is still in sequence.

 

[Andrew Fergus]

My initial test on text values returned the correct result. Have you got gaps in the data, or have you included any blank rows in your formula? Blank rows will return a result saying the data is not sorted correctly - I had assumed the data was continuous. Check you haven't included a blank row (either the first or last row) in the formula.

If you are already running a macro then the other way would be to test each value in the range using VBA.....that won't be difficult but may fail depending on the data quality and blank rows......can you provide an example of the value in column A?

Andrew

 

[clay5000]

I guess I am too green to understand what you are suggesting. The first range should compare greater than the second (lower set or rows)? It would seem that the values would be greater as you took a section from further down. Also, I didn't know "SUM" would work on text.

ps. the data is contiguous.

 

[lenze]

Why not just resort column A in your BeforePrint macro?

lenze

 

[clay5000]

Sometimes there are legitimate reasons for wanting an "ad hoc" view of the data, even though 90% of the time you are correct that it should be in the sequence of col A. I was hoping to provide for the 10% ad hoc. I actually have a "reset" button to put it back in sequence by col A.

 

[Andrew Fergus]

I guess I am too green to understand what you are suggesting. The first range should compare greater than the second (lower set or rows)? It would seem that the values would be greater as you took a section from further down. Also, I didn't know "SUM" would work on text.

ps. the data is contiguous.

Can you post the formula you used? And can you provide an example of one value from column A?

 

[vinod9111]

Andrew was not aware whether we could also check the sorting of the column until i cam accross this post .The formula works great, can you explain the logic of the formula for my understanding.

 

[Andrew Fergus]

The formula works great, can you explain the logic of the formula for my understanding.

This formula:
{=IF(SUM(--(A1:A9>A2:A10))=0,"Sorted","Not Sorted")}

can be broken down as follows:
A1:A9>A2:A10

Assuming the data is continuous in cells A1:A10 this part of the formula is comparing A1 against A2, A2 against A3, A3 against A4 and so on up to A9 against A10. The test A1>A2 returns TRUE if A1 exceeds A2, otherwise FALSE. If the data was sorted (in ascending order) then the array would look like this:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

If the data was not sorted (let's assume A3<a2 and="" a9=""><a8) then="" the="" array="" would="" look="" like="" this:=""> < A2 and A9 < A8) then the array would look like this:
{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

The next part is:
--
The minus minus converts TRUE to 1 and FALSE to 0. Assuming the data was sorted the array would then be converted as follows:
{0;0;0;0;0;0;0;0;0;0}
otherwise it may look like this (if not sorted per my example):
{0;1;0;0;0;0;0;0;1;0}

The Sum function returns the sum of the array, in the first instance it returns
Sum({0;0;0;0;0;0;0;0;0;0}) = 0
or (if not sorted)
Sum({0;1;0;0;0;0;0;0;1;0}) = 2

The IF statement then says if the sum = 0 then the list is sorted, otherwise it isn't. You can check out the workings of a formula by selecting the cell and then selecting menu option Tools > Formula Auditing > Evaluate Formula. I wouldn't recommend doing this with large array formulas because I have seen Excel crash on occasion if the array is too large.

Technically the formula doesn't need 2 minus symbols - you could get away with using just one. I'm just in the habit of using 2. There would be other ways of doing this with a formula but this method sprung to mind when I read the question this morning. However, I believe this formula isn't necessarily right for the problem at hand given the OP is already using VBA which wasn't stated in the first post......

Andrew</a8)></a2>