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.

elv
  • 85
  • 6
  • 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. – Lux Claridge Jun 19 '18 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. – elv Jun 19 '18 at 14:27
  • 1
    Excel has simple built-in duplicate-removing and merging/consolidation functionality. – ashleedawg Jun 19 '18 at 14:31
  • Possible duplicate of [Excel - Combine multiple columns into one column](https://stackoverflow.com/questions/2977660/excel-combine-multiple-columns-into-one-column) – ashleedawg Jun 19 '18 at 15:20

0 Answers0