Oct 02 2020 08:51 AM
Oct 02 2020 09:00 AM
SolutionLet's say the names are in A2:A100 and D2:D50.
In B2 (or another cell in row 2), enter the formula
=ISERROR(MATCH(A2,$D$2:$D$50))
This will return TRUE if the value of A2 does not occur in B2:B50, FALSE otherwise.
Fill down to row 100.
You can also use this formula in a conditional formatting rule for A2:A100 of type 'Use a formula to determine which cells to format' to highlight the non-matching names.
Oct 02 2020 09:05 AM
Array formula:
{=INDEX(A:A;SMALL(IF(COUNTIF(B$1:B$99;A$1:A$99)=0;ROW($1:$99));ROW()))}
and copy it down
* Array Formula is a special type of formula that must be entered by pressing Ctrl+Shift+Enter.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Oct 02 2020 09:52 AM
Taking @Hans Vogelaar sample, on modern Excel that could be
=FILTER(A2:A100,COUNTIF(D2:D50,A2:A100)=0)
assuming you have no blank cells in ranges, otherwise it'll be bit more complex.
Oct 02 2020 10:02 AM
Oct 02 2020 10:05 AM
Hi there,
You may use the following formula to compare two lists (Tables). Sample table is attached:
1 - COUNTIF($A$2:$A$17,B2) & COUNTIF($B$2:$B$17,A2)
2 - =IF(COUNTIF($A$2:$A$17,B2)=0,ROW(B2)) & =IF(COUNTIF($B$2:$B$17,A2)=0,ROW(A2))
3 - =IFERROR(INDEX($B$2:$B$17,SMALL($I$2:$I$19,L2)-1),"") & =IFERROR(INDEX($A$2:$A$17,SMALL($J$2:$J$19,L2)-1),"")
4 - =IFERROR(INDEX($B$2:$B$17,SMALL(IF(COUNTIF($A$2:$A$17,B2:B17)=0,ROW(B2:B17)), L2)-1),"") & =IFERROR(INDEX($A$2:$A$17,SMALL(IF(COUNTIF($B$2:$B$17,A2:A17)=0,ROW(A2:A17)), L2)-1),"") (Ctrl+Shift+Enter)
At the same time, you can do it using Conditional Formatting:
Picture is attached
Please follow the below written link (in Russian) for detailed explanation:
https://www.youtube.com/watch?v=c_ydkYhcXUI
Oct 13 2020 01:50 PM
Oct 14 2020 07:33 AM
Let;s say that the first list is on Sheet1 in A2:A100, and the second list on Sheet2 in A2:A50.
Enter the following array formula in B2 on Sheet2, confirmed with Ctrl+Shift+Enter (you may not need this in Excel 365):
=ISERROR(MATCH(1,IF(SUBTOTAL(3,OFFSET(Sheet1!$A$2:$A$100,ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2),0,1))>0,IF(Sheet1!$A$2:$A$100=A2,1)),0))
Fill down to B50.
Oct 14 2020 07:39 AM
Oct 14 2020 07:57 AM
I'm afraid I don't understand. Can you explain in detail what exactly you want to accomplish?
Oct 02 2020 09:00 AM
SolutionLet's say the names are in A2:A100 and D2:D50.
In B2 (or another cell in row 2), enter the formula
=ISERROR(MATCH(A2,$D$2:$D$50))
This will return TRUE if the value of A2 does not occur in B2:B50, FALSE otherwise.
Fill down to row 100.
You can also use this formula in a conditional formatting rule for A2:A100 of type 'Use a formula to determine which cells to format' to highlight the non-matching names.