SOLVED

Looking for help on what Function to use

%3CLINGO-SUB%20id%3D%22lingo-sub-1739862%22%20slang%3D%22en-US%22%3ELooking%20for%20help%20on%20what%20Function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739862%22%20slang%3D%22en-US%22%3EI%20have%20two%20columns%20of%20names%20in%20excel.%20Column%201%20has%20all%20names%20%2C%20column%202%20has%20a%20majority%20of%20names.%20What%20function%20can%20I%20use%20to%20see%20which%20names%20in%20column%201%20don%E2%80%99t%20appear%20in%20column%202%3F%20Thank%20you%20for%20any%20guidance!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1739862%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1739884%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20help%20on%20what%20Function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F818035%22%20target%3D%22_blank%22%3E%40John_Murphy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20names%20are%20in%20A2%3AA100%20and%20D2%3AD50.%3C%2FP%3E%0A%3CP%3EIn%20B2%20(or%20another%20cell%20in%20row%202)%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DISERROR(MATCH(A2%2C%24D%242%3A%24D%2450))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20will%20return%20TRUE%20if%20the%20value%20of%20A2%20does%20not%20occur%20in%20B2%3AB50%2C%20FALSE%20otherwise.%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20row%20100.%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20use%20this%20formula%20in%20a%20conditional%20formatting%20rule%20for%20A2%3AA100%20of%20type%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'%20to%20highlight%20the%20non-matching%20names.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1739888%22%20slang%3D%22de-DE%22%3ESubject%3A%20Looking%20for%20help%20on%20what%20function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739888%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F818035%22%20target%3D%22_blank%22%3E%40John_Murphy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EArray%20formula%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%A2%3DINDEX(A%3AA%3B%20SMALL(IF(COUNTIF(B-1%3AB-99%3B%20A-1%3AA-99)%3D0%3B%20ROW('1%3A'99))%3B%20ROW%20()))%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Eand%20copy%20it%20down%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22ILfuVd%22%3E%3CSPAN%20class%3D%22hgKElc%22%3E%3CSTRONG%3E*%20Array%20Formula%3C%2FSTRONG%3E%20is%20a%20special%20type%20of%20%3CSTRONG%3Eformula%3C%2FSTRONG%3E%20that%20must%20be%20entered%20by%20pressing%20Ctrl%2BShift%2BEnter.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740010%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20help%20on%20what%20Function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F818035%22%20target%3D%22_blank%22%3E%40John_Murphy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETaking%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bsample%2C%20on%20modern%20Excel%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(A2%3AA100%2CCOUNTIF(D2%3AD50%2CA2%3AA100)%3D0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eassuming%20you%20have%20no%20blank%20cells%20in%20ranges%2C%20otherwise%20it'll%20be%20bit%20more%20complex.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740019%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20help%20on%20what%20Function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740019%22%20slang%3D%22en-US%22%3EThank%20you!%20Very%20helpful%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740020%22%20slang%3D%22en-US%22%3EBetreff%3A%20Looking%20for%20help%20on%20what%20Function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740020%22%20slang%3D%22en-US%22%3EThank%20you%20Very%20Helpful%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740021%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20help%20on%20what%20Function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740021%22%20slang%3D%22en-US%22%3EThank%20you.%20Love%20this%20site.%20So%20amazing%3C%2FLINGO-BODY%3E
Occasional Contributor
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!
11 Replies
Best Response confirmed by John_Murphy (Occasional Contributor)
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.

@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.

 

@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.

Thank you! Very helpful
Thank you Very Helpful
Thank you. Love this site. So amazing

@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

 

 

Несколько способов сравнить два списка в Excel между собой и быстро найти разницу между ними (цветом или формулами). Подробная статья и пример для скачивания...
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!

@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.

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

@John_Murphy 

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