Home Pricing Community Teams About Start Free Trial

excelismagic

asked on 4/11/2017

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

Microsoft OfficeMicrosoft ExcelSpreadsheets

Last Comment

Shums Faruk

8/22/2022 - Mon

Shums Faruk

4/11/2017

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

4/11/2017

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?

excelismagic

4/11/2017

ASKER

COLUMNA is not a value it is a header

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Shums Faruk

4/11/2017

Corrected....
EE_4.xlsx

excelismagic

4/11/2017

ASKER

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

excelismagic

4/11/2017

ASKER

if you drag your formula down, why does it list the values that are not common between both columns?

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Shums Faruk

4/11/2017

Have checked in corrected sheet?
EE_4.xlsx

ASKER CERTIFIED SOLUTION

Professor J

4/11/2017

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

SOLUTION

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Certified Expert Program

Credly Partnership