Can I rank spatial data?

Johnny_H

New Member
Joined
Jan 23, 2014
Messages
12
Hi all,

I have a set of spatial data (X,Y,Z) that I wish to rank in excel. The aim is to be able to list the data points from East to West, North to South (and if possible North-East to South-West but this is less important for now). An added difficulty with this is that not all data points are unique, some points have the same X and Y with only a difference in Z value, in this instance I would want the data point with the highest Z value to be ranked the highest.

Is there some sort of spatial indexing tool that I can use to do this?

Hope all this is clear!

Cheers,

Jon
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
It's just a sorting problem, right?

Ignoring Z for the moment, how would you sort a list of x,y values to get the order you want?
 
Upvote 0
I have a few different situations but I want to sort from East to West as well as from North to South...

E.g. Using just X and Y, if I have data spatially arranged as below (x marks the point) I want to rank them to the following numbers in ''.

x'3' x'1'

x'4' x'2'

x'5'
 
Upvote 0
You can sort as many ways as you like, but you have to choose an order. It's conceptually impossible have a list that is sorted both E-W and N-S. To pick the simplest example, how should this be sorted?

[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][/tr]
[tr][td]
1​
[/td][td]
x​
[/td][td]
y​
[/td][/tr]

[tr][td]
2​
[/td][td]
1​
[/td][td]
2​
[/td][/tr]

[tr][td]
3​
[/td][td]
2​
[/td][td]
1​
[/td][/tr]
[/table]
 
Upvote 0
Thanks shg, just to be clear is your top row the excel column letters and your first column the excel row numbers? X and Y therefore being the title row of the table? If that is the case then the numbers would be sorted 2, 1, 1, 2, that is to say B2, B3, A2, A3 in terms of excel cell references.

Could a solution be...

-Rank my x column from largest to smallest
-In the case of a duplicate result in the X column have some kind of if statement that if the value in the cell below is the same as the target cell then it looks to the corresponding values in the Y column. The overall rank would then be applied to the Highest X value with the highest Y Value.

[TABLE="width: 326"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD] X[/TD]
[TD] Y[/TD]
[TD][/TD]
[TD]Desired Rank[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD] 192558.3 [/TD]
[TD] 57642.097[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD] 192558.3[/TD]
[TD] 57647.097[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD] 192550.8[/TD]
[TD] 57534.597[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]5 [/TD]
[TD] 192550.8[/TD]
[TD] 57524.597[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

In the above example the X values in the A column are sorted from largest to smallest.
 
Upvote 0
For your example, do a two-way sort, descending by x and descending by y.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top