Cmpare Two Lists

Excel777

Well-known Member
Hello all
i have two lists list 1 and list 2 i want to get results in two columns.

column 1 results give me the values that is found in list 1 and not found in list 2
column 2 results give me the values that is found in list 2 and not found in list 1
i wish the answer to be with one formula with out helper columns
thanks

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
EDIT
IGNORE not available in 2019 version

column 1 results give me the values that is found in list 1 and not found in list 2
=FILTER(A2:A9,COUNTIF(B2:B9,A2:A9)=0)

column 2 results give me the values that is found in list 2 and not found in list 1
=FILTER(B2:B9,COUNTIF(A2:A9,B2:B9)=0)

I think those functions are available in 2019, but may be only available in version 365, EDIT - should have looked up first , as it appears only in 365 versions
So sorry about that

Book10
ABCDE
1List1List2In List1, Not 2inlist2 not 1
2zaz12
3b12d2
4c2X14
5d14f3
6X3
7f5
85c
9ab
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=FILTER(A2:A9,COUNTIF(B2:B9,A2:A9)=0)
E2:E5E2=FILTER(B2:B9,COUNTIF(A2:A9,B2:B9)=0)
Dynamic array formulas.

I will be seeing family for a few days, back on 30th Dec

So i may not reply now until then - sorry if i have misunderstood - or leave hanging

Hopefully other members will pickup before

etaf thank you very much it works and merry Christmas

oh,
i did edit, as i have looked up and FILTER() is not available in 2019 - according to the web
Do you have 2019 version or 365
perhaps the info is out of date on the web

anyway
you are welcome

Replies
3
Views
31
Replies
1
Views
56
Replies
7
Views
112
Replies
2
Views
172
Replies
2
Views
82

1,203,140
Messages
6,053,729
Members
444,681
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back