Extracting items not on both lists

[tigerzen]

Looking for a formula to pull out the records shaded in red.

Football stats functions.xlsx
	A	B	C	D	E
1	Group 1			Group 2
2	Jack	Ken		Peter	Vic
3	Tom	Sally		Tom	Fred
4	Peter	Vic		Jane	Milton
5	Jane			Sally
Sheet17

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E5	Cell Value	unique values	text	NO

 

[Phuoc]

Try this formula:

=COUNTIF($A$2:$E$5,A2)=1

 

[Peter_SSs]

One way:

23 09 09.xlsm
	A	B	C	D	E	F	G
1	Group 1			Group 2			List
2	Jack	Ken		Peter	Vic		Jack
3	Tom	Sally		Tom	Fred		Ken
4	Peter	Vic		Jane	Milton		Fred
5	Jane			Sally			Milton
6
Sheet5

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(VSTACK(TOCOL(A2:B5,1),TOCOL(D2:E5,1)),,1)
Dynamic array formulas.

Or IF there is no data between the two lists and a name will not appear more than once in a list ..

23 09 09.xlsm
	A	B	C	D	E	F	G
1	Group 1			Group 2			List
2	Jack	Ken		Peter	Vic		Jack
3	Tom	Sally		Tom	Fred		Ken
4	Peter	Vic		Jane	Milton		Fred
5	Jane			Sally			Milton
6
Sheet6

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(TOCOL(A2:E5,1),,1)
Dynamic array formulas.

 

[tigerzen]

Try this formula:

=COUNTIF($A$2:$E$5,A2)=1

Hi Phuoc, thanks for that response, I'd actually started with something similar myself. Your formula does return the correct values as TRUE/FALSE but I can't get it to convert them into actual names such as Fred etc. I've been unsuccessful so far in finding out how to do this. I've tried using IF, SUMPRODUCT but nothing has worked for me yet, would like to know how to resolve this.

 

[tigerzen]

One way:

23 09 09.xlsm
	A	B	C	D	E	F	G
1	Group 1			Group 2			List
2	Jack	Ken		Peter	Vic		Jack
3	Tom	Sally		Tom	Fred		Ken
4	Peter	Vic		Jane	Milton		Fred
5	Jane			Sally			Milton
6
Sheet5

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(VSTACK(TOCOL(A2:B5,1),TOCOL(D2:E5,1)),,1)
Dynamic array formulas.

Or IF there is no data between the two lists and a name will not appear more than once in a list ..

23 09 09.xlsm
	A	B	C	D	E	F	G
1	Group 1			Group 2			List
2	Jack	Ken		Peter	Vic		Jack
3	Tom	Sally		Tom	Fred		Ken
4	Peter	Vic		Jane	Milton		Fred
5	Jane			Sally			Milton
6
Sheet6

Cell Formulas
Range	Formula
G2:G5	G2	=UNIQUE(TOCOL(A2:E5,1),,1)
Dynamic array formulas.

Thanks Peter, very clever and appreciate the solution variation in the event that the datasets are contiguous.

 

[Peter_SSs]

You're welcome.

 

[alansidman]

Late to the game, but thought I would show you an alternative with Power Query which may be effective if you have some very large tables.

Book2
	A	B	C	D	E	F	G
1	Group 1			Group 2
2	Column1	Column2		Column1	Column2		Missing
3	Jack	Ken		Peter	Vic		Fred
4	Tom	Sally		Tom	Fred		Milton
5	Peter	Vic		Jane	Milton		Jack
6	Jane			Sally			Ken
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    Source2=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(Source2, {}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute"}),
    MQ = Table.NestedJoin(#"Removed Columns", {"Value"},  #"Removed Columns1", {"Value"}, "TableX", JoinKind.LeftAnti),
    #"Expanded TableX" = Table.ExpandTableColumn(MQ, "TableX", {"Value"}, {"Value.1"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded TableX",{"Value.1"}),
    MQ2 = Table.NestedJoin(#"Removed Columns", {"Value"},  #"Removed Columns1", {"Value"}, "TableY", JoinKind.RightAnti),
    #"Expanded TableY" = Table.ExpandTableColumn(MQ2, "TableY", {"Value"}, {"Value.1"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded TableY",{"Value"}),
    MQ3 = Table.NestedJoin(#"Removed Columns2", {"Value"},  #"Removed Columns3", {"Value.1"}, "TableY", JoinKind.FullOuter),
    #"Expanded TableY1" = Table.ExpandTableColumn(MQ3, "TableY", {"Value.1"}, {"Value.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableY1", "Missing", each if [Value.1]= null then [Value] else [Value.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Missing"})
in
    #"Removed Other Columns"

 

[tigerzen]

Late to the game, but thought I would show you an alternative with Power Query which may be effective if you have some very large tables.

Book2
	A	B	C	D	E	F	G
1	Group 1			Group 2
2	Column1	Column2		Column1	Column2		Missing
3	Jack	Ken		Peter	Vic		Fred
4	Tom	Sally		Tom	Fred		Milton
5	Peter	Vic		Jane	Milton		Jack
6	Jane			Sally			Ken
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    Source2=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(Source2, {}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute"}),
    MQ = Table.NestedJoin(#"Removed Columns", {"Value"},  #"Removed Columns1", {"Value"}, "TableX", JoinKind.LeftAnti),
    #"Expanded TableX" = Table.ExpandTableColumn(MQ, "TableX", {"Value"}, {"Value.1"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded TableX",{"Value.1"}),
    MQ2 = Table.NestedJoin(#"Removed Columns", {"Value"},  #"Removed Columns1", {"Value"}, "TableY", JoinKind.RightAnti),
    #"Expanded TableY" = Table.ExpandTableColumn(MQ2, "TableY", {"Value"}, {"Value.1"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded TableY",{"Value"}),
    MQ3 = Table.NestedJoin(#"Removed Columns2", {"Value"},  #"Removed Columns3", {"Value.1"}, "TableY", JoinKind.FullOuter),
    #"Expanded TableY1" = Table.ExpandTableColumn(MQ3, "TableY", {"Value.1"}, {"Value.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableY1", "Missing", each if [Value.1]= null then [Value] else [Value.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Missing"})
in
    #"Removed Other Columns"

Thanks Alan, appreciate this as I do have some files with large tables.

 

[alansidman]

Thanks for the feedback.