Feb 19 2022 03:33 PM
Feb 19 2022 03:33 PM
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.
Feb 19 2022 10:33 PMSolution
@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).
Feb 20 2022 09:02 AM
Feb 20 2022 12:12 PM
Feb 20 2022 09:50 PM
@Dave_Reiner Fixed point 1, but can't replicate what you describe in point 2, or I just misunderstand.
Feb 21 2022 04:01 AM
@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.
Feb 22 2022 12:33 PM
@Riny_van_Eekelen Thanks! Concatenation works in some cases, but not for States.
Going back to spreadsheet 2 (without concatenation) my final problem (I hope it's the last one!) is that sometimes State is missing from List1 or List2 or both. I'd like to grab State from List1 if it's there, or from List2 if it's not in List1, or just have nothing in the cell if State is in NEITHER list. Is that possible?
Feb 22 2022 12:36 PM
@Riny_van_Eekelen Forgot screenshot. The 0 result for state of aaaa is the problem.
Feb 22 2022 10:15 PM
@Dave_Reiner It's getting less elegant for every exception, but I beehive the attached version works as you want it.
Feb 23 2022 08:16 PM