Looking for help on what Function to use

[John_Murphy]

I have two columns of names in excel. Column 1 has all names , column 2 has a majority of names. What function can I use to see which names in column 1 don’t appear in column 2? Thank you for any guidance!

[#2 · Accepted solution]

@John_Murphy 

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.

[#3]

@John_Murphy 

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.

[#4]

@John_Murphy 

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.

[#5]

Thank you! Very helpful

[#6]

Thank you Very Helpful

[#7]

Thank you. Love this site. So amazing

[#8]

@John_Murphy 

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

[#9]

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!

[#10]

@John_Murphy 

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.

[#11]

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

[#12]

@John_Murphy 

I'm afraid I don't understand. Can you explain in detail what exactly you want to accomplish?