how to extract unique list from two columns

excelismagic
excelismagic used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
ShumsManaging Director/Excel VBA Developer
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
excelismagic

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?
excelismagic

Author

Commented:
COLUMNA is not a value it is a header
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Corrected....
EE_4.xlsx
excelismagic

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

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
Distinguished Expert 2018

Commented:
Have checked in corrected sheet?
EE_4.xlsx
Microsoft Excel Expert
Top Expert 2014
Commented:
try this one when you want to list only those values that are common between two columns 

=IFERROR(INDEX(List1, SMALL(IF(COUNTIF(List2, List1), MATCH(ROW(List1),ROW(List1)), ""), ROWS($A$1:A1))),"")

Open in new window

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
The corrected method:
List1 NamedRange Formula:
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$1001))

Open in new window

List2 NamedRange Formula:
=OFFSET(Sheet2!$A$2, 0, 0, COUNTA(Sheet2!$A$2:$A$1001))

Open in new window

Array Formula for Unique List for both the list:
=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

Please find attached....where I have checker formula to check unique list. which is 42 in your case...
EE_Unique-list-multiple-Sheets.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial