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!

 

[sandy666]

Try Power Query

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"Name"},Table2,{"Name2"},"Table2",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Table2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [YN] <> "No")
in
    #"Filtered Rows"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]YN[/td][td][/td][td=bgcolor:#5B9BD5]Name2[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]YN[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Tom[/td][td=bgcolor:#DDEBF7]No[/td][td][/td][td=bgcolor:#DDEBF7]Tom[/td][td][/td][td=bgcolor:#E2EFDA]Davin[/td][td=bgcolor:#E2EFDA]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Davin[/td][td]Yes[/td][td][/td][td]Dave[/td][td][/td][td]Rashaad[/td][td]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Dave[/td][td=bgcolor:#DDEBF7]No[/td][td][/td][td=bgcolor:#DDEBF7]Bill[/td][td][/td][td=bgcolor:#E2EFDA]Grace[/td][td=bgcolor:#E2EFDA]Yes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Rashaad[/td][td]Yes[/td][td][/td][td]Sue[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Bill[/td][td=bgcolor:#DDEBF7]No[/td][td][/td][td=bgcolor:#DDEBF7]Sandy[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Grace[/td][td]Yes[/td][td][/td][td]Mark[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sue[/td][td=bgcolor:#DDEBF7]Yes[/td][td][/td][td=bgcolor:#DDEBF7]Rod[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sandy[/td][td]No[/td][td][/td][td]Henry[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mark[/td][td=bgcolor:#DDEBF7]Yes[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Emerlin]

Thank you for your help - I will have to look into Power Query as I have never used it.

I was thinking an array index match formula of some kind

 

[Scott T]

Book1
	B	C
2	Tom	No
3	Davin	Yes
4	Dave	No
5	Rashaad	Yes
6	Bill	No
7	Grace	Yes
8	Sue	Yes
9	Sandy	No
10	Mark	Yes
Sheet1

Book1
	B	C
2	Tom	Davin
3	Dave	Rashaad
4	Bill	Grace
5	Sue
6	Sandy
7	Mark
8	Rod
9	Henry
Sheet2

Cell Formulas
Range	Formula
C2	{=IF(ROWS(C$2:C2)>SUMPRODUCT(--((IF(Sheet1!$C$2:$C$10="yes",IF(COUNTIF(Sheet2!$B$2:$B$9,Sheet1!B$2:$B$10)=0,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1)))<>FALSE)),"",INDEX(Sheet1!$B$2:$B$10,SMALL(IF(Sheet1!$C$2:$C$10="yes",IF(COUNTIF(Sheet2!$B$2:$B$9,Sheet1!B$2:$B$10)=0,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1)),ROWS($C$2:C2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sandy666]

Thank you for your help - I will have to look into Power Query as I have never used it.

I was thinking an array index match formula of some kind

No problem
Have a nice day

 

[Emerlin]

Book1
	B	C
2	Tom	No
3	Davin	Yes
4	Dave	No
5	Rashaad	Yes
6	Bill	No
7	Grace	Yes
8	Sue	Yes
9	Sandy	No
10	Mark	Yes
Sheet1

Book1
	B	C
2	Tom	Davin
3	Dave	Rashaad
4	Bill	Grace
5	Sue
6	Sandy
7	Mark
8	Rod
9	Henry
Sheet2

Cell Formulas
Range	Formula
C2	{=IF(ROWS(C$2:C2)>SUMPRODUCT(--((IF(Sheet1!$C$2:$C$10="yes",IF(COUNTIF(Sheet2!$B$2:$B$9,Sheet1!B$2:$B$10)=0,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1)))<>FALSE)),"",INDEX(Sheet1!$B$2:$B$10,SMALL(IF(Sheet1!$C$2:$C$10="yes",IF(COUNTIF(Sheet2!$B$2:$B$9,Sheet1!B$2:$B$10)=0,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1)),ROWS($C$2:C2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you for this - it looks like solution. I will implement and report back with any problems.

Much appreciated!

 

[Aladin Akyurek]

Book1
	A	B
2	Tom	No
3	Davin	Yes
4	Dave	No
5	Rashaad	Yes
6	Bill	No
7	Grace	Yes
8	Sue	Yes
9	Sandy	No
10	Mark	Yes
Sheet1

Book1
	A	B
2	Tom	Davin
3	Dave	Rashaad
4	Bill	Grace
5	Sue
6	Sandy
7	Mark
8	Rod
9	Henry
Sheet2

In b2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$10,SMALL(IF(ISNA(MATCH(Sheet1!$A$2:$A$10,$A$2:$A$9,0)),IF(Sheet1!$B$2:$B$10="yes",ROW(Sheet1!$A$2:$A$10)-ROW(Sheet1!$A$2)+1)),ROWS($B$2:B2))),"")

 

[Emerlin]

Thank you for this - it looks like solution. I will implement and report back with any problems.

Much appreciated!

Thanks again for this - but I could not get it working.

 

[Emerlin]

Book1
	A	B
2	Tom	No
3	Davin	Yes
4	Dave	No
5	Rashaad	Yes
6	Bill	No
7	Grace	Yes
8	Sue	Yes
9	Sandy	No
10	Mark	Yes
Sheet1

Book1
	A	B
2	Tom	Davin
3	Dave	Rashaad
4	Bill	Grace
5	Sue
6	Sandy
7	Mark
8	Rod
9	Henry
Sheet2

In b2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$10,SMALL(IF(ISNA(MATCH(Sheet1!$A$2:$A$10,$A$2:$A$9,0)),IF(Sheet1!$B$2:$B$10="yes",ROW(Sheet1!$A$2:$A$10)-ROW(Sheet1!$A$2)+1)),ROWS($B$2:B2))),"")

Thank you for this - it works well except the it includes, as an example, sue in column b sheet 2 even though she is already on column b sheet 1? Any ideas?

What I have now dynacmically creats a list of any players on BOTH lists that have YES. I am trying to find the YES on sheet one that does not appear on sheet 2.

Thanks.

 

[Aladin Akyurek]

Thank you for this - it works well except the it includes, as an example, sue in column b sheet 2 even though she is already on column b sheet 1? Any ideas?

What I have now dynacmically creats a list of any players on BOTH lists that have YES. I am trying to find the YES on sheet one that does not appear on sheet 2.

Thanks.

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