How do I do the opposite of FILTER formula in Cell L2?

[arthurz11]

arthurz11​

Did You try to answer to my two questions?

Yes, I only want to use the FILTER formula to get the results. Please note that djc post #24 wrote two FILTER formulas and both FILTER formulas displays only the x,y duplicates. But I also need the formula to display only the x,y non-duplicates. The Excel worksheet that better explains what I need it to do.

 

[djc]

Your original formula can be modified to return the x,y pairs that appear only once by setting the optional [exactly_once] argument of the UNIQUE function to 1 or TRUE:

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    u, UNIQUE(INDEX(f,,s),,TRUE),
    n, SEQUENCE(COUNT(u),,0),
    TRANSPOSE(INDEX(u,INT(n/2)+1,MOD(n,2)+1))
)

 

[arthurz11]

Very nicely done!!!! Test met all the requirements. Thank you soooooo much. Where do I click to show a solution to the problem has been solved?

 

[djc]

You're welcome, and thanks for the feedback. I don't think there is any such "mark as solved" feature on this forum. You can choose to "Like" a response if you want to, but it's not a big deal to me... I'm just happy to help. Cheers!

 

[djc]

As an afterthought, you could also eliminate the final TRANSPOSE function by utilizing the [columns] argument (instead of rows) in the final SEQUENCE function:

=LET(
    ...
    n, SEQUENCE(,COUNTA(u),0),
    INDEX(u,INT(n/2)+1,MOD(n,2)+1)
)

This can be applied to each of the working formula variations discussed thus far.

 

[arthurz11]

As an afterthought, you could also eliminate the final TRANSPOSE function by utilizing the [columns] argument (instead of rows) in the final SEQUENCE function:

=LET(
    ...
    n, SEQUENCE(,COUNTA(u),0),
    INDEX(u,INT(n/2)+1,MOD(n,2)+1)
)

This can be applied to each of the working formula variations discussed thus far.

Thank you again. However, I am very satisfied with the formula using the FILTER function. FYI: I have inform (with a link) to all other Excel website forums of you (djc from Chandoo.org) for creating a solution to the formula problem.

 

[djc]

No worries, you don't have to use it. However, just to clarify, the "afterthought" was not a replacement for the FILTER function, but rather a simplification of the final two steps. The ellipsis (...) was meant to represent everything that came before the final two steps in each of the working formulas discussed thus far. The suggested revisions in their entirety would look like this:

Distinct List (original):

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    u, UNIQUE(INDEX(f,,s)),
    n, SEQUENCE(,COUNTA(u),0),
    INDEX(u,INT(n/2)+1,MOD(n,2)+1)
)

Unique List (only once):

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    u, IFERROR(UNIQUE(INDEX(f,,s),,1),""),
    n, SEQUENCE(,COUNTA(u),0),
    INDEX(u,INT(n/2)+1,MOD(n,2)+1)
)

Duplicates List (version 1):

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    v, INDEX(f,,s),
    x, INDEX(v,,1),
    y, INDEX(v,,2),
    u, UNIQUE(FILTER(v,MMULT((x=TRANSPOSE(x))*(y=TRANSPOSE(y)),IF({1},1,x))>1,"")),
    n, SEQUENCE(,COUNTA(u),0),
    INDEX(u,INT(n/2)+1,MOD(n,2)+1)
)

Duplicates List (version 2):

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    v, INDEX(f,,s),
    a, UNIQUE(v),
    b, UNIQUE(v,,1),
    u, FILTER(a,ISERROR(XMATCH(INDEX(a,,1)&"|"&INDEX(a,,2),INDEX(b,,1)&"|"&INDEX(b,,2))),""),
    n, SEQUENCE(,COUNTA(u),0),
    INDEX(u,INT(n/2)+1,MOD(n,2)+1)
)

Notice how COUNTA(u) is placed in the [columns] argument of SEQUENCE, eliminating the need for TRANSPOSE in the final step.

 

[arthurz11]

Hey! I just noticed an error when there is no data (all blanks in a row). I have forgotten that sometimes I do not get any x,y coordinates. When I was doing the long version, in one of the rows the data was all blanks. Please try it, delete the data in range A2:J2 and the results in TABLE TWO will result in the error #CALC?. So it gave me #CALC? error on the formulas below. Where do I put the IFERROR on each formula so that I don't get the #CALC? error? If you place the IFERROR at the beginning of the formula, will it be place at the same spot for the other formulas? Below are the formulas you wrote in which I decided to use on my worksheet. I appreciate your energy and time in helping me.

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    v, INDEX(f,,s),
    x, INDEX(v,,1),
    y, INDEX(v,,2),
    u, UNIQUE(FILTER(v,MMULT((x=TRANSPOSE(x))*(y=TRANSPOSE(y)),IF({1},1,x))>1,"")),
    n, SEQUENCE(COUNTA(u),,0),
    TRANSPOSE(INDEX(u,INT(n/2)+1,MOD(n,2)+1))
)

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    v, INDEX(f,,s),
    x, INDEX(v,,1),
    y, INDEX(v,,2),
    u, UNIQUE(FILTER(v,MMULT((x=TRANSPOSE(x))*(y=TRANSPOSE(y)),IF({1},1,x))>1,"")),
    n, SEQUENCE(COUNTA(u),,0),
    TRANSPOSE(INDEX(u,INT(n/2)+1,MOD(n,2)+1))
)

=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    u, UNIQUE(INDEX(f,,s),,TRUE),
    n, SEQUENCE(COUNT(u),,0),
    TRANSPOSE(INDEX(u,INT(n/2)+1,MOD(n,2)+1))
)

 

[djc]

The simplest fix that should work for all three formulas is to put {"",""} in the [if_empty] argument of the first FILTER function:

=LET(
    f, FILTER(A2:J2,A2:J2<>"",{"",""}),
    ...
)

However, you will also need to use COUNTA(u) instead of COUNT(u) with the last SEQUENCE function.

 

[vletm]

arthurz11​

Okay, only formula solution.

It's good that You wrote to update other sites which You've cross-posted this case.

It's common sense to let others know where and especially: what did You expect?​

After You've done above.

You can add to end of Your thread title [SOLVED]​

( or I can add it )​