Forum Discussion
Selecting or Filtering Entire Rows from a Table
I am trying to figure out how to select or filter out entire rows from a table. I have tried the FILTER function, and the MAP function paired with INDEX, MATCH, but nothing seems to work. It seems as...
I did find one solution that works, but it required the creation of a helper table. Here is the solution that worked, and below, I will post the formula for the helper table.
=LET(N, SEQUENCE(Nall), M, MATCH(N, N),
Cta, COUNTA(INDEX(Taxing_Authorities_List,,2)),
Nta, SEQUENCE(1, Cta), Mta, MATCH(Nta, Nta),
TIF_YN, INDEX(TIF_Info, M, 1),
Include, BYCOL(IF(TIF_YN = "Yes", 1, 0) * INDEX(TALT_Table, M, Mta), LAMBDA(X, IF(SUM(X) > 0, 1, 0))),
TANames, TRANSPOSE(MAKEARRAY(Cta, 1, LAMBDA(r,c, INDEX(Taxing_Authorities_List,r,2)))),
TRANSPOSE(FILTER(TANames, Include = 1, "None"))
)
And here is the formula for the helper table. For this table, the formula is by row. Each row represents one Property ID Number (PIN). I then selected the entire resulting table for all of the PINs and named it as a named range in the Formulas menu. This is what I called the TALT_Table, as used in the above formula.
=LET(
Cta, COUNTA(INDEX(Taxing_Authorities_List,,2)),
TANames, TRANSPOSE(MAKEARRAY(Cta, 1, LAMBDA(r,c, INDEX(Taxing_Authorities_List,r,2)))),
Ntag, SEQUENCE(1,COLUMNS(TAGtable)), Mtag, MATCH(Ntag, Ntag),
X, LAMBDA(X, ISNUMBER(MATCH(TANames,INDEX(TAGtable,X,Mtag),0))*1)(MATCH($A2,PIN_PrIn,0)),
IF($A2 <>"", X, "")
)