Comparing two columns of names and returning missing names

Hello,

 

Using Excel 2007 in French.

Column A has alphanumerical values from A1:A1800

Column B has alphanumerical values from B1:B380

 

Most of the cells contain a first part "F13450" where the number following the letter  F changes through the whole column A but what follows this is a phrase, like (example: F13450-Apple_brown_english.dotx)

 

In column B, I will probably will have the same data (example: F13450-Apple_brown_french.dotx) But the difference will be in the name. In this example, the difference is the word "french"

 

Basically, i would like to know which file name (these are actually Word files in two separate directory on my PC) I do not have in my column B. Of course I do not have the same amount of lines which is normal. I would like Excel to indicate which "F13450" file is missing from my list in B.  I hope this was clear enough!

 

Thanks!

 

Luke

Answer
Answer

.. Basically, i would like to know which file name (these are actually Word files in two separate directory on my PC) I do not have in my column B ...


This is primarily regarded as a database function and is much more efficiently handled through a database program, but you can imitate the functionality with a worksheet.

             

The formula in C2 is,
=IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")
... which is an array formula and needs to be finalized with Ctrl+Shift+Enter↵ rather than simply Enter↵. Once properly entered, the formula can be filled down to catch all missing document titles. 
I believe I've provided cell ranges that will cover the ones you described in your original post, but you should double-check to be sure. Note that as an array formula referencing 1800+ rows, you can expect some calculation time.

I have uploaded the sample workbook to my SkyDrive here.

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated June 30, 2020 Views 7,286 Applies to: