Loop through table and find max number of condition fulfillment

Hi Igor,

But ... have you tried to find just 1 such situation in your file?
вы не найдете

So either the conditions as described are wrong, or were written with error.

To test I changed row 44 data to:

43 5190 5200

and then output is Unit9
as for i = 9
there is B10 = 5195
and it is between
B44 and C44

Please Login or Register  to view this content.

Hi:

As for point 2 - it's already there array my_arr
is loaded with values from B2 to Dn, where n is last filled row

Please Login or Register  to view this content.

it's like you select D1048576 and press Ctrl+UpArrow
Try it. select D1048576 and press Ctrl+UpArrow. As you can see with your file - selection is now D57 - last filled cell in column D.

Point 1 does not matter: there are zeros or positive values in columns B and C. and if Bx is 0 then Cx is also 0. So while looping there will never be situation that Cy>Bx>By for any y because there is no By <0. (every Bi is >=0). Also there will be no Cx>By>Bx because with Bx = 0 Cx is also = 0 so there will be no value whic could be bigger than 0 and smaller than 0.

Point 3 is easy, but only once there is data which fulfills requirement
But your data do not.

The code could be:

Please Login or Register  to view this content.

But ... there is no data fulfilling the requirement. Please have a look on your data and judge yourself - which rows/units should be included. The code finds no such cases when

Cj>Bi>Bj

Even if we allow i<j (we do not - as per request) there is no match.

I'd say points 1 and 3 are simple mathematics. We cannot change it

So in row 12 you have
8290 8380 CR3
and in row 20:
8370 8460 CR2
x = 12, y = 20, and "old reqirement" Cy>Bx>By
is not met here.
However, the "new requirement" (Cy>Cx>By) is met, so CR3 (row 12) will be listed after inserting this new requirement into the code:

Please Login or Register  to view this content.

Finding CR2 (row 20) would be against what you wrote before, about searching only below. But changing one loop (searching in j rows (j is always below i)

Please Login or Register  to view this content.

into searching in all above (from first to i-1) and below (form i+1 to last row)

Please Login or Register  to view this content.

would yield the result you found manually.

Ну, наконец это работает. Сначала у нас были проблемы с получением. Но в итоге это удалось.

Большое спасибо за оценку

(I hope I can still remember some Russian from my school times - not too often used nowadays )