Forum Discussion
Smart consolidation of 2 email lists? Help requested...
Dave_Reiner You didn't mention the Excel version you work with so I'm hoping it's ms365 or 2021. If not, kindly ignore this.
The attached file contains a possible solution using the UNIQUE and FILTER functions. Append both lists into one and create a structured table from it (Ctrl-T). Add a column that designates the source (List 1 or List 2).
1) If there's an email in List2 that does not occur in List1, then the Filter function that fetches State [from List1] gives a CALC error. In this case, I would want to get the value of State from List2.
2) If the State values are different for the same email in List1 and List2 [in my real spreadsheet this actually happens with a First Name column], then two values are returned and I get a SPILL error.
Suggestions?
Thanks,
Dave
Dave_Reiner Fixed point 1, but can't replicate what you describe in point 2, or I just misunderstand.
See attached.
Riny_van_Eekelen Thanks for the fix to point 1. I appreciate your work!
For point 2, here's an example where an email appears twice in List1, with a different value for state. This causes the SPILL error. This situation happens once in a while in my data (it's not perfect!). Perhaps there's a fix to the formula (e.g., take the first one), or perhaps it points to records that need to be manually edited or combined in the source list with an intelligent choice about which value seems more likely.Dave_Reiner Try the attached. It concatenates both states.