I have a table similar to the one below, apart from its about 600 rows long:
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:
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.
3
When using a table you can use this formula:
=IF([@Level]=4,
LET(dataKnots,TAKE([Knots],ROW()-1),
dataLevels,TAKE([Level],ROW()-1),
d,REDUCE("",SEQUENCE(5,1,4,-1),
LAMBDA(r,l,HSTACK(r,TAKE(FILTER(dataKnots,dataLevels=l),-1)))),
TEXTJOIN(",",TRUE,d)),
"")
It checks the data above (and including) the current row.
Within the REDUCEpart each previous level is filtered and stacked to the result.
2
With listing level as parameter, otherwise similar logic to others. Using Microsoft 365 (for LET, LAMBDA etc.):
=LET(
row_num, ROW() - ROW(Table1[#Headers]),
list_level, 4,
list_levels, SEQUENCE(list_level + 1, , list_level, -1),
IF(
[@Level] = list_level,
ARRAYTOTEXT(
XLOOKUP(
list_levels,
TAKE([Level], row_num),
TAKE([Knots], row_num),
,
0,
-1
)
),
""
)
)
As range
=LET(
last_row, COUNTA(A:A),
knots, A2:INDEX(A:A, last_row),
level, B2:INDEX(B:B, last_row),
list_level, 4,
list_levels, SEQUENCE(list_level + 1, , list_level, -1),
list_knots, LAMBDA(level_, row_num,
IF(
level_ = list_level,
ARRAYTOTEXT(
XLOOKUP(
list_levels,
TAKE(level, row_num),
TAKE(knots, row_num),
,
0,
-1
)
),
""
)
),
MAP(level, SEQUENCE(ROWS(knots)), list_knots)
)
For Excel 2021
Replace TAKE with INDEX:
=LET(
row_num, ROW() - ROW(Table2021[#Headers]),
list_level, 4,
list_levels, SEQUENCE(list_level + 1, , list_level, -1),
IF(
[@Level] = list_level,
ARRAYTOTEXT(
XLOOKUP(
list_levels,
INDEX([Level], 1, 1):INDEX([Level], row_num, 1),
INDEX([Knots], 1, 1):INDEX([Knots], row_num, 1),
,
0,
-1
)
),
""
)
)
4
=MAP(A2:A10,B2:B10,LAMBDA(a,b,IF(b=4,ARRAYTOTEXT(XLOOKUP({4,3,2,1,0},B2:b,A2:a,,,-1)),"")))
It – if current row in column B is 4 – looks back for last 4, 3, 2, 1, 0 from current row and up and returns the corresponding value in column A. Wrapped in a join function (ARRAYTOTEXT)
You could replace ARRAYTOTEXT with TEXTJOIN in case you don’t want spaces in between values:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,IF(b=4,TEXTJOIN(",",,XLOOKUP({4,3,2,1,0},B2:b,A2:a,,,-1)),"")))
Or turn it into a table and use: =IF([@Level]=4,TEXTJOIN(",",,XLOOKUP({4,3,2,1,0},INDEX([Level],1):[@Level],INDEX([Knots],1):[@Knots],,,-1)),"")
2
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)), "")
)