• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining of 2 Lists

L

leimst

Member
Good Afternoon All,


I have 2 lists of names, each on it's own tab. Some names are on both lists while other names are only on one of the lists. I would like a formula that would look at each of the lists and return a cumulative list of names with no duplicates.


Any help would be greatly appreciated!


Brian
 
Hi, leimst!


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


If I don't remember wrongly there are a lot of examples here in this website, even articles under Formula Forensic.


Regards!
 
Hello,


I have combed through search results for combining 2 lists but I'm not seeing any examples where 2 separate lists are combined and duplicates removed. I was hoping for a formula that would eliminate the need for someone unfamiliar with Excel to manually combine the lists and remove the duplicates. If this is not possible using formulas would it be best to approach the solution using VBA maybe?


Thanks!
 
Hi leimst!


If I understand correctly you want to compare two lists and get the unique list from the two.


Suppose, you have list in one tab(List1) and another list in other tab(list2) as follows:


at list1:

A

A

B

B

C

C

D

D

F

F

and list2:

A

B

A

C


Formula at a new tab (unique) (at a2) would be:


=IFERROR(IFERROR(INDEX(dynlist1,MATCH(0,COUNTIF($A$1:A1,dynlist1),0)),INDEX(dynlist2,MATCH(0,COUNTIF($A$1:A1,dynlist2),0))),"") press CTRL+SHIFT+ENTER to enter it as an array formula (not just enter) and drag it down as needed.


where dynlist1 and dynlist2 are the dynamic named ranges (press CTRL+F3 from key board and select the names to examine the named range formulae)


I have uploaded the workbook here:

http://speedy.sh/Pqgun/For-leimst-get-unique-list-Chandoo-forum.xlsx.xlsx


Please check and let us know if this what you are looking for. If this is fine, we can then talk about the explanation behind this formula, if you need.


Regards,

Kaushik
 
Kaushik,


Thank you very much for your help and I apologize for the delay in getting back to you as I was pulled away for another project. This works great and after examining your formula I believe it to be comparable to a VLOOKUP command? Which VLOOUP (Index(Match(x)) is executed is dependent on the first IFERROR function which is included in a second IFERROR statement which ultimately returns a blank once all unique values are discovered?


Thanks again, leimst
 
You must log in or register to reply here.
Back
Top