hi,
i'm looking to merge two lists of names into one combined list with only unique values.
the two lists have mostly the same info but list 1 contains some info not in list 2 and visa versa.
i need it so that each list can be updated and the combined list will automatically update itself.
can this be done??
Rob
Hi rbncpr,
Yes it can be done. Please attach sample worksheet. You can use "&" to combine two names.
Regards,
Merge lists.xlsx
here's an example of what i'm trying to do!
hi rbncpr,
This can be done but you will use another column but if you don't prefer using another column.I think VBA code is needed for this.
What do you prefer?
Thanks
i have no problem with using another column, i'd like to avoid VBA if possible and keep it with formulae!
Hi rbncpr
this formula will work on your sample worksheet,but if you will add data,i think you also need to change conditions in the formula.
on C3, paste this formula =IF(COUNTIF(list1,A2)=1,A2,IF(COUNTIF(list2,B2)=1,B2,"")) on D3,paste this formula,=IF(COUNTIF($C2:$C$15,C2)=1,C2,"")
you will see your required result in D3
Hi,
Go to Name Manager and define Rowz as:
=ROW(INDIRECT("1:"&ROWS(list1)*2))
Your array formula** is then:
=IFERROR(INDIRECT(TEXT(SMALL(IF(FREQUENCY(IF($A$2:$B$15<>"",MATCH($A$2:$B$15,T(OFFSET($A$2,INT((Rowz-1)/2),MOD(Rowz-1,2),,)),0)),Rowz),(10^3*(ROW($A$2)+INT((Rowz-1)/2)))+1+MOD(Rowz-1,2)),ROWS($1:1)),"R000C000"),0),"")
Copy down until you start to get blanks for the results.
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Use this formula in C2
=IFERROR(INDEX(list1,MATCH(0,INDEX(COUNTIF($C$1:C1,list1),,),)),IFERROR(INDEX(list2,MATCH(0,INDEX(COUNTIF($C$1:C1,list2),,),)),""))
List 1 List 2 List 3 AAB AAC AAD AAE BBA BBC zaza zzz AAF CCA BBD BBG poof Pran
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I should've mentioned that I was attempting to design a solution which could be extended (with suitable modifications) to an arbitrary number of columns, not just two.
If you in fact will only ever have two, then it would make sense to take e.g. AlKey's solution, which is simpler and less resource-heavy.
Regards
thanks everyone for yere help... i finally have it working the way i wanted! AlKey's solution is working well, cheers!!
Please close the thread if you got your solution.
You can have a look at this also in the mean time
Please make the Post as solved, when you get your answer & Click * if you like my suggestion
You're welcome and thank you for the feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved)
hi all
Does anyone know how to do the above with the new dynamic array formulas?
Administrative Note:
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks