Forum Discussion
Excel Help
I was sent a contact information list & the same name is listed multiple times with different emails. (Each person has more than 1 email address.) If we upload into our system as is, it creates a separate account for every email. I need to get rid of duplicate contact names, without losing the extra email addresses. I’m a novice in Excel, help!
You don't mention your version of Excel. The attached sheet shows a method that will work with a Microsoft 365 subscription or Excel 2021 or newer.
Two formulas are needed.
=SORT(UNIQUE([list of names])), e.g., =SORT(UNIQUE(A1:A2000))
=TRANSPOSE(FILTER(Table1[Emails],Table1[Names]=E2))
the first formula, the one with "unique" in it, only is entered once; the results all spill to rows below.
the second formula needs to be copied to cells adjacent to the list of unique names
And of course, if John Smith is listed as John Smith each time, that's not a problem, but if he's sometimes John Q. Smith, then that is a different name as far as Excel is concerned. So you may need to do some cleaning up when that occurs.
This solution might be more than what's needed but it's fun to put together because 365 makes it easier.
ReShape(data) =LET( rng, DROP(data, 1), header, TAKE(data, 1), name, TAKE(rng, , 1), uName, SORT(UNIQUE(name)), email, TAKE(rng, , -1), organize, LAMBDA(a, v, LET(records, FILTER(email, name = v, ""), IFERROR(VSTACK(a, HSTACK(v, TOROW(records))), "")) ), REDUCE(header, uName, organize) )=IFERROR(INDEX($B$2:$B$15,SMALL(IF($A$2:$A$15=$D2,ROW($A$2:$A$15)-1),COLUMN(A$1))),"")An alternative with e.g. Excel 2013 could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
The formula is in cell E2 and then filled across range E2:I6 in the example.
