how to extract unique list from two columns
I have column A in sheet1 and Column A in Sheet2
in sheet3 Column A i want to list unique list of all values that exists in Sheet1 ColA and Sheet2 ColA
please see attached.
EE.xlsx
in sheet3 Column A i want to list unique list of all values that exists in Sheet1 ColA and Sheet2 ColA
please see attached.
EE.xlsx
Create two named range list with below formula:
List1
List1
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A))
=IFERROR(IFERROR(INDEX(List1,MATCH(0,COUNTIF($B$1:B1,List1),0)),INDEX(List2,MATCH(0,COUNTIF($B$1:B1,List2),0))),"")
thanks Shams
but there is one problem with the solution. why should i have an empty row above sheet3? cannot i have it the way i portrayed it?
but there is one problem with the solution. why should i have an empty row above sheet3? cannot i have it the way i portrayed it?
sorry both of the answers are incorrect. when i drag down the formula it also lists the values that are not in common.
if you drag your formula down, why does it list the values that are not common between both columns?
