Max of row based on pre-set dynamic conditions

Hi guys,

I have a slightly complicated issue that I need help with automating. The sheet I’m referring to is attached with this post.
Basically, I have a table comprising cells B2:T26 and I need to retrieve values in the adjoining table under column W (Incremental Amount).

However, the values that I need to retrieve will vary from row-to-row according to the below:
• The value in W4 is the max of cells C4:T4 ie “78” (D4)
• The value in W5 is the max of cells (C4,E4:T4,D5) (since D4 has already been retrieved in step 1. This value is retrieved as “77” (D5)
• Similarly, the value in W6 is the max of cells (C4, E4:T4,D6) (since D4 and D5 have already been retrieved in step 1 and step 2 respectively. This value is retrieved as “89” (D6)
• The above steps will continue, and at each step, the first value in each column should be considered in the MAX formula unless it has already been utilized, in which case the next cell should be considered.
Please see the formulae in cells W4:W195 incase this isn’t clear.
At some point, there may be 2 or 3 values which would each fulfill the MAX criteria. In this case, we need to prioritize in ascending order (ie Item A over Item B over Item C…..)

Once this has been done, we then need to identify the cell address corresponding to the values retrieved in column W. Or alternatively retrieve the Item , Type and Value for this cell. (Perhaps this could be included in the formula??)
I’ve been doing this manually till now, but see that there could be some element of automation to this.

Any help with a formulae or some VBA script would be appreciated?

Please Login or Register  to view this content.

RUN button to execute macro

Results in column AA and AB

NOTE: there is currently no check for duplicates.

QUESTION: What is test for "completion": macro current executes the rows you have determined i.e finishes on row 171

How do you calculate the "VALUE" in column Z or is it just a constant of 100? and I assume "TYPE" correlates to the "Cell Address"

And can you post a file with one (or more) duplicates (preferably near the beginning of the data to help make testing easier!)

And it is not clear how to can do 800 iterations when you have < 500 cells?

What happens when we reach row 26 for any column?

Attached is an example of simply looping 800 times: I deleted the erroneous value resulting form values in rows > 26