Possible? Finding max value of prices a set distance from a cell starting from right to left (columns are price,qty,rating,rating,repeat)

[Waboku]

Hello, Happy Holidays.

I would like to better analyze data for my work. I don't know how to do this but I would like to know if it is possible and if anyone doesn't mind I would appreciate help with a function that can do this. My data set is 300 columns, and 18000 rows. For this example below the data is 3 by 12.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Buy Price 1
[/TD]
[TD]Buy Qty 1
[/TD]
[TD]Rating 1
[/TD]
[TD]Rating 2
[/TD]
[TD]Buy Price 2
[/TD]
[TD]Buy Qty 2
[/TD]
[TD]Rating 1
[/TD]
[TD]Rating 2
[/TD]
[TD]Buy Price 2
[/TD]
[TD]Buy Qty 2
[/TD]
[TD]Rating 1
[/TD]
[TD]Rating 2
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I would like to be able to do is use a function find the first rating from right to left, then if the rating is in the rating 1 column find the max value of the price 2 prices to the left of the rating and of all the prices to the right of that price. If the rating is in a rating 2 column I would like to find the max value of the price to the left of the rating and of all the prices to the right of that price. If you have the time please consider helping me with my problem. Thanks.

 

[Aladin Akyurek]

What are the expected values for the sample you posted?

 

[Waboku]

Hello Aladin Akyurek, thanks for considering helping me with my problem. I try not to post questions I can do my self. When I didn't hear back from anyone for some time after posting this I redid the format of my data so I think I no longer need help with this. Thanks again.