Create list from two columns of different lengths match two criteria?

[Emerlin]

Hi,

That title is probably not clear.... sorry in advance. I hope this will clear it up.

I have Column B with 30 rows of Data and a Unique ID in Column "C" yes for example. This column is shorter in length than on Sheet 2 and contains a unique identifier in column 2.

Sheet 1
Column B, Column C
Tom, No
Davin, Yes
Dave, No
Rashaad, Yes
Bill, No
Grace, Yes
Sue, Yes
Sandy, No
Mark, Yes


column B may have 20 names with the ID of yes

Sheet 2 contains a longer list of names in column B with NO identifier. It is a much longer list.
Column B
Tom
Dave
Bill
Sue
Sandy
Mark
Rod
Henry
etc..... to 100 names

On Sheet 2 I want to create a list, a dynamic list that creates of a list of the names the DO NOT appear on the longer list, but contain the Identifier of "yes" from sheet 1

As in my example - the created list in column C on sheet 2 would equal to
Column C
Grace
Rashaad
Davin

because the names to not appear on Sheet 2 column B, and they have the Unique ID on Sheet 1 column C. I am using the unique ID on sheet one to apply conditional formatting on the sheet 2 list, but it is not long enough to contain some for the more esoteric data on sheet one. So I need a dynamic exception list.

I am pretty sure this has to be an array formula. I have been tried sever things but decided this is beyond me and would like some help. Thanks in advance for any help and guidance!

 

[Emerlin]

I don't get Sue at all (as my exhibit also implies). The set up does exacly what you are asking: "

I am trying to find the YES on sheet one that does not appear on sheet 2", that is, the sublist of the names of Sheet1 with yes which are not in Sheet2.
​

Thank you again. I will check my syntax again and get back to you. I probably have something entered incorrectly.

 

[Aladin Akyurek]

Thank you again. I will check my syntax again and get back to you. I probably have something entered incorrectly.

See the workbook which implements the proposal: https://www.dropbox.com/s/aqw4lzxnse22zw7/Emerlin sublist.xlsx?dl=0

 

[Emerlin]

Thank you so much!!!!

I did indeed have a column selected incorrectly and this helped very much.

You are a gentlemen and a scholar.

 

[Aladin Akyurek]

You are welcome. Thanks for the kind feedback.