Unique Filter Not Working

[garypea123]

Good Evening,

Unfortunately I do not have XL2BB and cannot download.

Possibly just a tired mind but I am having an issue with my filter not creating a unique reference only.

What I would like ideally is for the filter to bring only anything which is in Pick (Column K) but I do not want a duplicate entry in from Column L.

As you can see below Order 1 is appearing twice, but I would only want one showing (with no duplicate entries)

Can anyone offer any advise, I tried to add another parameter but do not know

 

[garypea123]

I think I have fixed it anyway. It creates the reference as Column O should be the same. So if 128 appears in 1st Order, then logic is that 128 should appear in the second instance. Any deviation to values will mean it will create a reference

 

[Fluff]

How about

=FILTER(J2:O100,(K2:K100=C2)*(MMULT((SEQUENCE(99)>=SEQUENCE(,99))*(L2:L100=TRANSPOSE(I2:I100)),SEQUENCE(99,,,0)))=1,"")

 

[garypea123]

Thank you Fluff, It works but the formula makes no sense to me (Would not know where to begin with that one) .

I have a fix in place for mine which I hope will not fall (but one which I understand)

Would love to learn more in terms of your formula. Hopefully there are some books around.

 

[Fluff]

There's a typo in the formula & it should be

=FILTER(J2:O100,(K2:K100=C2)*(MMULT((SEQUENCE(99)>=SEQUENCE(,99))*(L2:L100=TRANSPOSE(L2:L100)),SEQUENCE(99,,,0)))=1,"")

The MMULT function is doing the same as if you put

=COUNTIFS(L$2:L2,L2)

in P2 & dragged down, so the filter only picks up the 1st instance of each order number