Need Help Updating a List

%3CLINGO-SUB%20id%3D%22lingo-sub-2426232%22%20slang%3D%22en-US%22%3ENeed%20Help%20Updating%20a%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2426232%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20master%20lists.%20One%20containing%20old%20data%20that%20hots%20contact%20info%20that%20has%20fields%20such%20as%20%22name%22%2C%20%22number%22%20%22address%22%2C%20etc.%20Then%20another%20list%20with%20all%20those%20contacts%20plus%20some%20new%20ones%20with%20the%20same%20fields.%20Is%20there%20a%20way%20I%20can%20combine%20these%20lists%20and%20essentially%20only%20find%20and%20replace%20the%20duplicates%20with%20the%20newly%20updated%20information%20so%20I%20can%20update%20these%20contacts%20that%20have%20changed%20their%20address%20or%20number%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBonus%3A%20If%20possible%20I%20also%20want%20to%20have%20some%20indicator%20or%20value%20assigned%20to%20contacts%20that%20did%20not%20have%20a%20duplicate%2C%20so%20I%20can%20make%20a%20smaller%20list%20titled%20%22New%20Contacts%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2426232%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-2426443%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20Updating%20a%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2426443%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1073712%22%20target%3D%22_blank%22%3E%40Wilberto757%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%20it's%20possible%20to%20do%20what%20you're%20asking.%20I'm%20pretty%20sure%20you'll%20get%20some%20suggestions%20about%20how%20Power%20Query%20can%20do%20it.%20I%20work%20on%20a%20Mac%20and%20Power%20Query%20isn't%20available%20yet%20on%20Macs%2C%20so%20I%20will%20have%20to%20point%20you%20in%20a%20different%20direction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithout%20actually%20seeing%20how%20your%20data%20are%20arrayed%2C%20it's%20hard%20to%20give%20really%20specific%20guidance.%20And%20you%20don't%20say%20how%20experienced%20you%20are%20with%20Excel.%20So%20what%20I%20give%20you%20will%20presume%20you%20are%20familiar%20enough%2C%20or%20confident%20enough%2C%20to%20do%20some%20experimenting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELast%20year%20in%20the%20newest%20updates%20to%20Excel%20some%20functions%20were%20introduced%20that%20should%20enable%20you%20to%20accomplish%20what%20you're%20trying%20to%20do.%20Here's%20a%20YouTube%20video%20that%20explains%20them%2C%20with%20some%20good%20demonstrations.%20I%20have%20in%20mind%20in%20particular%20the%20%3CSTRONG%3EUNIQUE%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20functions.%20Used%20properly%2C%20you%20should%20be%20readily%20able%20to%20accomplish%20the%20tasks%20you've%20described.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20need%20further%20help%2C%20may%20I%20suggest%20that%20you%20post%20some%20samples%20(rendered%20anonymous)%20of%20the%20actual%20spreadsheets%20you%20have.%20Change%20names%20and%20other%20identifiable%20characteristics.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20that%20video%20link%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

I have two master lists. One containing old data that hots contact info that has fields such as "name", "number" "address", etc. Then another list with all those contacts plus some new ones with the same fields. Is there a way I can combine these lists and essentially only find and replace the duplicates with the newly updated information so I can update these contacts that have changed their address or number?

 

Bonus: If possible I also want to have some indicator or value assigned to contacts that did not have a duplicate, so I can make a smaller list titled "New Contacts".

1 Reply

@Wilberto757 

 

Of course it's possible to do what you're asking. I'm pretty sure you'll get some suggestions about how Power Query can do it. I work on a Mac and Power Query isn't available yet on Macs, so I will have to point you in a different direction.

 

Without actually seeing how your data are arrayed, it's hard to give really specific guidance. And you don't say how experienced you are with Excel. So what I give you will presume you are familiar enough, or confident enough, to do some experimenting.

 

Last year in the newest updates to Excel some functions were introduced that should enable you to accomplish what you're trying to do. Here's a YouTube video that explains them, with some good demonstrations. I have in mind in particular the UNIQUE and FILTER functions. Used properly, you should be readily able to accomplish the tasks you've described.

 

If you need further help, may I suggest that you post some samples (rendered anonymous) of the actual spreadsheets you have. Change names and other identifiable characteristics.

 

Here's that video link: https://www.youtube.com/watch?v=9I9DtFOVPIg

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...