Forum Discussion
Smart consolidation of 2 email lists? Help requested...
I have two large email lists to consolidate. Each is in an Excel worksheet on a Mac. Some email addresses are duplicated across both lists, and some are on only one list.
I'll simplify this to make what I want to do clearer.
List one: email state donation interests
List two: email state donation interests
I'd like one consolidated list with the same fields, deduplicated so that each email address only appears once.
Where there's an email match, the state column should come from list one, and donation from list two.
Where there's an email match, interests should include the text from list one followed by the text from list two.
Rows that don't match should be added as they are to the consolidated list.
Is there a way to do this smart consolidation? Data Consolidate in Excel would let me sum values, but not make this kind of intelligent choice about where the consolidated column contents come from, or how text columns are combined.
Thanks!
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).
10 Replies
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).
- Riny_van_Eekelen Two subtle issues that I'm encountering in trying this out:
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,
DaveDave_Reiner Fixed point 1, but can't replicate what you describe in point 2, or I just misunderstand.
See attached.
- Riny_van_Eekelen I have a 365 subscription, and your example works correctly in my Excel version.
Now I just have to understand FILTER semantics a little better :-). Thanks so much!!
