0

I have two lists of company names, how can I merge them in the third list without duplicates. For example my list 1 is in row C36-C45, list 2 is in row C51-C60 and I would like to get the result of merging two list in row C66-C75. My lists are dynamic I get from the database, so sometimes, not all the ranges have company names.

I tried the below formula in cell C66 and dragged down, I get the values but starting from C67; in C66 I get an empty cell. Don't know what is the reason, any help?

=IFERROR(LOOKUP(2;1/(COUNTIF($C$65:C65; $C$36:$C$45)=0); $C$36:$C$45); LOOKUP(2;1/(COUNTIF($C$65:C65; $C$51:$C$60)=0); $C$51:$C$60))

UPDATE: I found the problem I have a list title in C65 if I delete that text, I don't get the empty cell in C66 but I would like to keep the title how can I update the function to be able to do that.

Improve this question
4
  • Do your records have ID numbers for companies? I found that within my company we can have multiple lists of vendors (for some weird reason) and some vendors exist in both lists, however the names don't match perfectly (e.g. leaving out "Inc" or "LLC") so we found it's better to sort by ID since that's less likely to have minor differences. Commented Jun 19, 2018 at 14:21
  • Hi Lux, thanks for your comment, I don't have IDs but in my lists, they match perfectly as for both list I take the company names from the same list, so I don't think this is the problem. Commented Jun 19, 2018 at 14:27
  • 1
    Excel has simple built-in duplicate-removing and merging/consolidation functionality. Commented Jun 19, 2018 at 14:31
  • Possible duplicate of Excel - Combine multiple columns into one column Commented Jun 19, 2018 at 15:20
Related questions
0
Pulling two lists into one, and showing duplicates
1
Merge two lists in Excel in ascending or descending order
1
Merge two sheets and keep the redundant data
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.