Link to home
Start Free TrialLog in
Avatar of fb1990
fb1990

asked on

Extract Unique List when in Two Columns in Excel

Hello EE,

Can someone please help me with a formula that will extract list of names of present in both columns.  I have a list of names in columns A and B.  I want extract a list of names that is present in both columns A and B into column C.  I have attached an example data here

Thanks in advance for your help.
Sample.xlsx
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi,

First select range of Column A as per your data A2:A123 and navigate to Formula/NameRange, click New, Name the range as NameList1, check it refers to the range you selected, do the same process for Column B and Name the range as NameList2, then in C2 apply below formula with Ctrl+Shift+Enter
=IFERROR(IF(ISERROR(INDEX(NameList1, MATCH(0, COUNTIF($C$1:C1, NameList1), 0))), INDEX(NameList2, MATCH(0, COUNTIF($C$1:C1, NameList2), 0)), INDEX(NameList1, MATCH(0, COUNTIF($C$1:C1, NameList1), 0))),"")

Open in new window

Please find attached for your reference
Unique-List--2-Columns--Sample.xlsx
Avatar of fb1990
fb1990

ASKER

Hello Shums,

Thanks for your input.  I am looking for 4 names to be extracted into columns C.  The names that I needed are highlighted in the sample data. I need the name ONLY if they are present in both columns
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

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
Please find attached for your reference
Unique-List--2-Columns--Sample.xlsx
Once you have Name List appearing in column like in earlier post, name that range as NameList3, then in Column D apply this formula:
=IFERROR(INDEX(NameList3,MATCH(0,COUNTIF($D$1:$D1,NameList3),0)),"")

Open in new window

Please find attached...
Unique-List--2-Columns--Sample.xlsx
See if this is what you are trying to achieve.
The attached contains UDF called GetUniqueList and used in the column C.
Get-Unique-List.xlsm
Avatar of fb1990

ASKER

Thanks you very much Sir.  Just what the Doctor ordered!  Much appreciated.
Here is another version with the changed order of names as per your requirement.
Get-Unique-List-v2.xlsm
Avatar of fb1990

ASKER

Hi Neeraj, Sorry I did not see you comment, before closing.  Thanks as always.
Never mind. You're welcome! :)
Neeraj Sir's solution is always best.

Thanks fb1990.
Avatar of fb1990

ASKER

haha,  your solution did the trick for me.
LOL, have you seen his solution?
I was trying to get through multiple formula and his one function did the trick. You should have waited for his solution, its not about points, its about best solution.
Hats off to Neeraj Sir.
Thanks Shums for appreciating the work. :)
Your formula already resolved the question and the OP is happy with it. :)
Avatar of fb1990

ASKER

I know Neeraj is the BEST!.  Shum, your solution did the trick for me as well.  Blended well with my convoluted spreadsheet.   Smile
You're welcome any time! Glad I was able to help.:)
Avatar of fb1990

ASKER

Neeraj and Shum... I have another question coming maybe you guys can can help me out again
Yes please
Avatar of fb1990

ASKER

Here is my new question.  Thanks in advance for helping crack the code
Avatar of fb1990

ASKER

I did not paste:
 https://www.experts-exchange.com/questions/29001687/Extract-Names-Based-on-Position-in-the-Column.html

Open in new window