SOLVED

New Contributor

difference between two filtered lists in excel

Hi,

I have two lists and I want to compare between them. However, I only want to compare between certain portions of each list. What I am really looking for is the difference between 2 filter functions. In my example below, I have 2 list and I filter each one based on the criteria of being "=1". I would like to create a function that gives me the difference between these two filter functions, in this case that would be "joe". Is this possible? Thank you in advance.

 list 1 filter list 1 list 2 filter list 2 bob 1 bob bob 1 bob tom 1 tom tom 1 tom dave 2 joe sam 3 joe 1 sam 3 =FILTER(A2:A6,B2:B6=1) =FILTER(F2:F6,G2:G6=1)
7 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

Re: difference between two filtered lists in excel

@abrummet That could be like in the picture below.

File attached.

Re: difference between two filtered lists in excel

@Riny_van_Eekelen
=FILTER(A:A,XLOOUP(A:A,B:B,B:B,X)=X)
Could X be any number? As I test 0,10,100... ^O^

Re: difference between two filtered lists in excel

@Benny_1857 I guess so. The "X" inside the XLOOKUP is what get's returned when the lookup value is not found. Then, with FILTER you can specify the "X" to be the inclusion criteria.

Re: difference between two filtered lists in excel

Thanks. I see what you are doing there and that makes sense; however, it doesn't quite work for what I am wanting to do. The xlookup embedded in the filter function is essentially just finding the difference between the two lists with no connection to the number associated with that person.  I am trying to find the difference between the two list for everyone that has a certain number associated with them. Meaning, who is a "1" on the first list and who is a "1" on the second list, and now what is the difference between them. Any thoughts on this? Thanks again.

Re: difference between two filtered lists in excel

unless I spoke too soon. I see now you did the filter for both and then the embedded xlookup and filter. This works! Thank you and I apologize for the confusion

Re: difference between two filtered lists in excel

Additionally, I simply embedded the two initial filter functions into the filter and xlookup combo and that worked wonderfully. So this is condensed down into 1 equation. Thanks!

Re: difference between two filtered lists in excel

@abrummet But that is exactly what the formula is doing. You asked for a formula that could determine the difference between two list, filtered for the number 1.

You can cut-out the interim steps to make an "all-in-one-formula" like this:

``=FILTER(FILTER(B2:B6,C2:C6=1),XLOOKUP(FILTER(B2:B6,C2:C6=1),FILTER(E2:E4,F2:F4=1),FILTER(E2:E4,F2:F4=1),1)=1)``

Make it easier to read/maintain by using named ranges, rather than direct cell references and a hard-coded numbers 1. Since you are on a recent Excel version, consider using LET to avoid the repeating portions in the formula. But perhaps I have missed your point all-together.

Edit: Didn't see your last two posts until I posted mine.