excelvbaexcel-formula

Excel(VBA) Formula in added column needing logical workaround

I have a table similar to the one below, apart from its about 600 rows long:

enter image description here

and I am trying to find an excel formula or even with VBA since in reality there are a lot of rows, to give me a resulting column as follows:

enter image description here

To elaborate, I would like a list of all the knots in the levels prior where the Level is 4. So to explicitly explain the last result we get 18, because that is the knot on that row, the next level 3 is where the knot is 14, then the level 2 is 12, and now comes the tricky bit..Since the level 4 and 3 have already been dedicated, we ignore all 4s, 3s, and 2s above, and consider the level 1 and 0, hence 4 and 2 to obtain the final result 18,14,12,4, and 2.

Is it possible, maybe by adding additional columns or with a VBA solution to achieve the result column?

I tried a formula in the reult column like tih: =IF(C9=4; CONCATENATE(“,", TRUE, B9:INDEX(B$1:B9; MAX(IF(C$1:C9=0; ROW(C$1:C9), IF(C$1:C9=1; ROW(C$1:C9); 0))))), ’”), which didn't work...

I couldn't get past that...

Thanks in advance.

By the way, I have Microsoft Excel 2021.

Solution

  • Thanks guys, but I got the answer myself. I can't believe how easy it was in the end:

    TEXTJOIN(",", TRUE,
    IF(B3=4, A3, ""),
    IF(B3=4, MAX(IF(($B$2:$B$18=3)*($A$2:$A$18<A3), $A$2:$A$18)), ""),
    IF(B3=4, MAX(IF(($B$2:$B$18=2)*($A$2:$A$18<A3), $A$2:$A$18)), ""),
    IF(B3=4, MAX(IF(($B$2:$B$18=1)*($A$2:$A$18<A3), $A$2:$A$18)), ""),
IF(B3=4, MAX(IF(($B$2:$B$18=0)*($A$2:$A$18<A3), $A$2:$A$18)), "")
)