Select value and return corresponding values associated within group of selected value

[DsIshukone]

Hi community,

I'm now facing a quite tricky situation that I will need help from you experts, so here's the situation:

I have a dataset, in this dataset there's ID, Long for longitude, Lat for Latitude, Pix (a coordinate in Lambert Conformal Conic format, it has 2 parts, one for x and one for y), and Score. Records with different Long and Lat will be grouped into the same Pix (e.g., Records 1 to 5 with same Pix x10000_y10000, even though their Long and Lat are totally different, Records 6 to 8 with same Pix x10800_y10000).

What I want to achieve, for examle if I select Pix equals x10800_y10000 (which is records 6 to 8), the results will return records 6 to 8, as well as records 1 to 5.

Let me explain, by selecting Pix x10800_y10000, I want to return all records based on 2 conditions:

1. whose Pix for x is within 1000 of 10800, meaning Pix for x is between 9800 and 11800; 

2. whose Pix for y is within 1000 of 10000, meaning Pix for y is between 9000 and 11000

So here, for records 1 to 5, their Pix is x10000_y10000, so the Pix for x is 10000, which is between 9800 and 11800; the Pix for y is 10000, also between 9000 and 11000, both conditions met, thus records 1 to 5 will be returned, along with records 6 to 8. Other records will not be returned, because at least 1 condition is not met.

Thank you in advance!

[lbendlin · Accepted solution]

[DsIshukone]

Sorry for late reply, this is a great solution, I never thought it would be that simple! Thank you very much for the help!

[DsIshukone]

Sample dataset looks like:

ID	Long	Lat	Pix	Score
1	10	10	x10000_y10000	123
2	10	11	x10000_y10000	456
3	10	12	x10000_y10000	789
4	11	11	x10000_y10000	321
5	12	11	x10000_y10000	654
6	20	20	x10800_y10000	1121
7	20	21	x10800_y10000	1123
8	21	21	x10800_y10000	1125
9	30	30	x11900_y10600	4022

10	30	31	x11900_y10600	4025
11	30	32	x11900_y10600	4028
12	31	30	x11900_y10600	4029
13	40	40	x20000_y10000	305
14	40	41	x20000_y10000	306
15	40	42	x20000_y10000	307
16	41	42	x20000_y10000	308

What I want it returns upon selecting Pix equals x10800_y10000:

ID	Long	Lat	Pix	Score
1	10	10	x10000_y10000	123
2	10	11	x10000_y10000	456
3	10	12	x10000_y10000	789
4	11	11	x10000_y10000	321
5	12	11	x10000_y10000	654
6	20	20	x10800_y10000	1121
7	20	21	x10800_y10000	1123
8	21	21	x10800_y10000	1125