We help IT Professionals succeed at work.

how to extract unique list from two columns

147 Views
Last Modified: 2017-04-25
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
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Author

Commented:
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?

Author

Commented:
COLUMNA is not a value it is a header
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Corrected....
EE_4.xlsx

Author

Commented:
sorry both of the answers are incorrect. when i drag down the formula it also lists the values that are not in common.

Author

Commented:
if you drag your formula down, why does it list the values that are not common between both columns?
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Have checked in corrected sheet?
EE_4.xlsx
Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION