Looking to find a formula that can do this [My Excel can use filter and unique formulas]-CodePudding

For each year, exclude all data from columns B and C that are identical to columns D and E respectively AND ignore all data that are blank. Then write down all data remaining without blank lines in between.

Anyone has an answer? thanks

CodePudding user response：

=FILTER(A2:C34,1-ISNUMBER(MATCH(A2:A34&"|"&B2:B34&"|"&C2:C34,A2:A34&"|"&D2:D34&"|"&E2:E34,0)))

CodePudding user response：

Lets try this way:

=LET(lookup_array, 
 BYROW(FILTER(FILTER(A2:E34, (D2:D34<>"") * (E2:E34<>"")),{1,0,0,1,1}), 
 LAMBDA(row, CONCAT(row))),
 exclude,ISNA(XMATCH(A2:A34&B2:B34&C2:C34, lookup_array)), 
 FILTER(A2:C34, exclude * (B2:B34<>"") * (C2:C34<>"")))

and here is the output:

Explanation

We are going to use XMATCH(lookup_value, lookup_array) concatenating both input arguments to exclude concatenated rows that matches. For the lookup_value is trivial, just using ampersand operator (&): A2:A34&B2:B34&C2:C34, but for the second argument we need to build it.

FILTER(FILTER(A2:E34, (D2:D34<>"") * (E2:E34<>"")),{1,0,0,1,1})

just filter the exclusion criteria removing empty ones (inner Filter) and just selecting Year, B and C columns (outer FILTER).

here the intermediate result:

2017    8   (E)
2017    12  (E)
2018    13  (Fx)
2018    46  (E)
2018    50  (E)
2018    8   (Fu)
2018    13  (Fu)
2019    8   (E)
2019    50  (E)
2019    8   (Fu)

we use BYROW to concatenate all columns by row:

BYROW(FILTER(FILTER(A2:E34, (D2:D34<>"") * (E2:E34<>"")),{1,0,0,1,1}), 
 LAMBDA(row, CONCAT(row)))

Now we have all input arguments ready for our XMATCH. Because we want to exclude, we are interested on #N/A values only. The final FILTER will do the rest, remove empty B and C rows and and the FALSE values from exclude variable.