SOLVED

Smart consolidation of 2 email lists? Help requested...

%3CLINGO-SUB%20id%3D%22lingo-sub-3188585%22%20slang%3D%22en-US%22%3ESmart%20consolidation%20of%202%20email%20lists%3F%20Help%20requested...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3188585%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20large%20email%20lists%20to%20consolidate.%20Each%20is%20in%20an%20Excel%20worksheet%20on%20a%20Mac.%20Some%20email%20addresses%20are%20duplicated%20across%20both%20lists%2C%20and%20some%20are%20on%20only%20one%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20simplify%20this%20to%20make%20what%20I%20want%20to%20do%20clearer.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EList%20one%3A%26nbsp%3B%3C%2FSTRONG%3Eemail%20%26nbsp%3Bstate%20%26nbsp%3Bdonation%20%26nbsp%3Binterests%20%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EList%20two%3A%3C%2FSTRONG%3E%20email%26nbsp%3B%26nbsp%3Bstate%20%26nbsp%3Bdonation%20%26nbsp%3Binterests%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20one%20consolidated%20list%20with%20the%20same%20fields%2C%20deduplicated%20so%20that%20each%20email%20address%20only%20appears%20once.%3C%2FP%3E%3CP%3EWhere%20there's%20an%20email%20match%2C%20the%20state%20column%20should%20come%20from%20list%20one%2C%20and%20donation%20from%20list%20two.%3C%2FP%3E%3CP%3EWhere%20there's%20an%20email%20match%2C%20interests%20should%20include%20the%20text%20from%20list%20one%20followed%20by%20the%20text%20from%20list%20two.%3C%2FP%3E%3CP%3ERows%20that%20don't%20match%20should%20be%20added%20as%20they%20are%20to%20the%20consolidated%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%20smart%20consolidation%3F%20Data%20Consolidate%20in%20Excel%20would%20let%20me%20sum%20values%2C%20but%20not%20make%20this%20kind%20of%20intelligent%20choice%20about%20where%20the%20consolidated%20column%20contents%20come%20from%2C%20or%20how%20text%20columns%20are%20combined.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3188585%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189060%22%20slang%3D%22en-US%22%3ERe%3A%20Smart%20consolidation%20of%202%20email%20lists%3F%20Help%20requested...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1312506%22%20target%3D%22_blank%22%3E%40Dave_Reiner%3C%2FA%3E%26nbsp%3BYou%20didn't%20mention%20the%20Excel%20version%20you%20work%20with%20so%20I'm%20hoping%20it's%20ms365%20or%202021.%20If%20not%2C%20kindly%20ignore%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20contains%20a%20possible%20solution%20using%20the%20UNIQUE%20and%20FILTER%20functions.%20Append%20both%20lists%20into%20one%20and%20create%20a%20structured%20table%20from%20it%20(Ctrl-T).%20Add%20a%20column%20that%20designates%20the%20source%20(List%201%20or%20List%202).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189991%22%20slang%3D%22en-US%22%3ERe%3A%20Smart%20consolidation%20of%202%20email%20lists%3F%20Help%20requested...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189991%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20I%20have%20a%20365%20subscription%2C%20and%20your%20example%20works%20correctly%20in%20my%20Excel%20version.%3CBR%20%2F%3ENow%20I%20just%20have%20to%20understand%20FILTER%20semantics%20a%20little%20better%20%3A-).%20Thanks%20so%20much!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3190270%22%20slang%3D%22en-US%22%3ERe%3A%20Smart%20consolidation%20of%202%20email%20lists%3F%20Help%20requested...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3190270%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20Two%20subtle%20issues%20that%20I'm%20encountering%20in%20trying%20this%20out%3A%3CBR%20%2F%3E1)%20If%20there's%20an%20email%20in%20List2%20that%20does%20not%20occur%20in%20List1%2C%20then%20the%20Filter%20function%20that%20fetches%20State%20%5Bfrom%20List1%5D%20gives%20a%20CALC%20error.%20In%20this%20case%2C%20I%20would%20want%20to%20get%20the%20value%20of%20State%20from%20List2.%3CBR%20%2F%3E2)%20If%20the%20State%20values%20are%20different%20for%20the%20same%20email%20in%20List1%20and%20List2%20%5Bin%20my%20real%20spreadsheet%20this%20actually%20happens%20with%20a%20First%20Name%20column%5D%2C%20then%20two%20values%20are%20returned%20and%20I%20get%20a%20SPILL%20error.%3CBR%20%2F%3E%3CBR%20%2F%3ESuggestions%3F%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3EDave%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3191201%22%20slang%3D%22en-US%22%3ERe%3A%20Smart%20consolidation%20of%202%20email%20lists%3F%20Help%20requested...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3191201%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1312506%22%20target%3D%22_blank%22%3E%40Dave_Reiner%3C%2FA%3E%26nbsp%3BFixed%20point%201%2C%20but%20can't%20replicate%20what%20you%20describe%20in%20point%202%2C%20or%20I%20just%20misunderstand.%3C%2FP%3E%3CP%3ESee%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192937%22%20slang%3D%22en-US%22%3ERe%3A%20Smart%20consolidation%20of%202%20email%20lists%3F%20Help%20requested...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20fix%20to%20point%201.%20I%20appreciate%20your%20work!%3CBR%20%2F%3EFor%20point%202%2C%20here's%20an%20example%20where%20an%20email%20appears%20twice%20in%20List1%2C%20with%20a%20different%20value%20for%20state.%20This%20causes%20the%20SPILL%20error.%20This%20situation%20happens%20once%20in%20a%20while%20in%20my%20data%20(it's%20not%20perfect!).%20Perhaps%20there's%20a%20fix%20to%20the%20formula%20(e.g.%2C%20take%20the%20first%20one)%2C%20or%20perhaps%20it%20points%20to%20records%20that%20need%20to%20be%20manually%20edited%20or%20combined%20in%20the%20source%20list%20with%20an%20intelligent%20choice%20about%20which%20value%20seems%20more%20likely.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202022-02-21%20at%206.54.30%20AM.png%22%20style%3D%22width%3A%20890px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349974iAE58CD5A9C2154B4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202022-02-21%20at%206.54.30%20AM.png%22%20alt%3D%22Screen%20Shot%202022-02-21%20at%206.54.30%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3193030%22%20slang%3D%22en-US%22%3ERe%3A%20Smart%20consolidation%20of%202%20email%20lists%3F%20Help%20requested...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3193030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1312506%22%20target%3D%22_blank%22%3E%40Dave_Reiner%3C%2FA%3E%26nbsp%3BTry%20the%20attached.%20It%20concatenates%20both%20states.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

 

10 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@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 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!!
@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,
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.Screen Shot 2022-02-21 at 6.54.30 AM.png

@Dave_Reiner Try the attached. It concatenates both states.

 

@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? 

@Riny_van_Eekelen Forgot screenshot. The 0 result for state of aaaa is the problem.

 

Screen Shot 2022-02-22 at 3.25.23 PM.png

@Dave_Reiner It's getting less elegant for every exception, but I beehive the attached version works as you want it.

@Riny_van_Eekelen Thank you - that works great! I still see occasional SPILL errors, but they point me to conflicts in the input data that I am able to fix in the structured table.