Sorting snake fashion

**okanagan** · 08-13-2021, 04:56 AM

I'd like to sort sets of xy coordinates in a snake fashion. Say the x coordinate represents a physical row and the y coordinate the location within the row. I want the list to sort first level by x and second level by y but I want the y sort order to reverse when x changes. The resulting list will be used to conduct a survey of the locations, allowing the surveyor to walk down one row and up the next. Sample file attached.
Would appreciate any ideas.

**XOR LX** · 08-13-2021, 07:59 AM

Hi,

You won't be able to sort the table within their original columns using formulas, though you can create a separate version of the table which meets the required sorting conditions, viz:

In H4, simply:

=A4

and copied down.

In I4, array formula**:

=SMALL(IF(A$4:A$73=H4,B$4:B$73),ABS(IF(ISODD(SUM(N(H$3:H3<>H$4:H4))),0,1+COUNTIF(H$4:H$73,H4))-COUNTIF(H$4:H4,H4)))

and copied down.

The formulas for column J I assume you can derive yourself?

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**FlameRetired** · 08-15-2021, 10:55 AM

Hello okanagan. Welcome to the forum.

Are you still using Office 2007? If not please update your profile. There are additional tools in newer versions. This helps us help you.

**okanagan** · 08-16-2021, 03:23 AM

Thanks XOR LX, that works great for the y coordinate column. I'm not versed in array formulas so I'll have to spend some time understanding how it works.

However, I can't get it to work for the value column, which I want to sort with the y coordinates, because each value column entry is an attribute of that particular xy coordinate. But when I adapt your formula for the value column, it sorts by the values.

No matter, I could get the values where I need them to be by using lookup functions.

After posting my question and before I got your reply, I continued to think about ways to do the sort and found another way to get the result I need. In case anyone is interested, I did it like this:
I added a temporary column in which I entered a formula that tests when the x coordinate changes and results in "D" for x coordinates that need regular low to high sorting and "U" for reverse, high to low sorting.
Like this in cell K5 of my file: =IF(AND(A5=A4,K4="D"),"D",IF(AND(A5=A4,K4="U"),"U",IF(K4="D","U","D"))) and copied to the bottom of the list, after "D" manually entered in K4.
Then I used a simple data filter and took advantage of how Excel only sorts the visible records of filtered lists.
So I filtered to show only the "U" rows, then sorted backwards, then unfiltered and had what I needed.

Thanks again.