Forum Discussion
Looking for help on what Function to use
Let'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.
11 Replies
- Thank you for all your help. The functions worked when I just created two quick test lists however when I wanted to use the function on my actual data it did not. The problem I am having is each column is filtered. For example 100 names in column A, I applied a filter so that only 30 of the 100 names show on the spreadsheet. When I go to compare the two columns using the functions everyone suggested it is somehow grabbing the entire unfiltered list and not just what’s showing. Instead of 1-30 being used is in the functions its automatically selecting basically 1-100 bc the last person may have been row 100 on the unfiltered list. How do I get my functions to use just what’s currently showing and not the unfiltered results. Thank you!
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.
- Thanks Hans but the issue I’m having is when I’m selecting A2-A100 it’s selected all values A2-A350 bc the last name was row 350 on the unfiltered original spreadsheet. How can I copy the list so it removes the original location on initial unfiltered spreadsheet or modify the function
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
Taking HansVogelaar 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.
- Thank you. Love this site. So amazing
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.
- Thank you Very Helpful
Let'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.
- Thank you! Very helpful
