Link to home
Start Free TrialLog in
Avatar of excelismagic
excelismagic

asked on

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
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Create two named range list with below formula:
List1
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))

Open in new window

List2
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A))

Open in new window

In Sheet3 B2 enter below Array Formula, confirmed with Ctrl+Shift+Enter:
=IFERROR(IFERROR(INDEX(List1,MATCH(0,COUNTIF($B$1:B1,List1),0)),INDEX(List2,MATCH(0,COUNTIF($B$1:B1,List2),0))),"")

Open in new window

EE_3.xlsx
Avatar of excelismagic
excelismagic

ASKER

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?
COLUMNA is not a value it is a header
Corrected....
EE_4.xlsx
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?
Have checked in corrected sheet?
EE_4.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial