Hi,



thanks in advance.

i want to find from the list the points which come in a particular region.


a1 3728 3077
a2 3867 3816
a3 3995 6134
a4 3995 6427
a5 3957 7534
a6 3874 7746
a7 3459 8373
a8 2925 9077
a9 2312 9077
a10 2298 9062
a11 1898 8361
a12 1667 7941

please look at the picture attached for my requirement.


Logic:

Find max x value and corresponding y. if more than 1 value is max go with max y value.
similarly do it for y
you have the rectangle. find the points which come within this range.

Attached Images

upload.jpg (41.6 KB, 15 views)

Not a simple problem. Just to kick off the conversation, the way I see it...

Find max(x).
Find all rows where x = max(x)
Of these, find the row which has the max(y).

For your data, this will be...
3995 6427

Find max(y).
Find all rows where y = max(y)
Of these, find the row which has the max(x).

For your data, this will be...
2925 9077

The rectangle you want will have vertices that will be the four permutations (or is that combinations ) of these...
3995 9077
2925 9077
3995 6427
2925 6427

I think the points within this range are easily determined using a sumproduct formula.

Finding the location of the first max(x) is easy. If the data are in A,B,C...

=MATCH(MAX(B:B),B:B,0) which will return 3 (the row where the first instance of 3995)

Now, how do we find the other maxima, if any? Once we figure that out, the rest should fall in place.

i am looking for a macro which does the same thing

I've made some progress on this! See attached.

Attached Files

Region.xlsx (14.8 KB, 9 views)

you man are awesome gebobs. even this would do. lemme give it a try.

thanks,
sandcharles

I know. I keep telling my wife that. ;-) TBH, I couldn't have done it without help from someone at the MrExcel message board.

If you need any further help, lemme know.