Sort, dedupe, merge and format in excel

[parfm]

I need to send out a holiday email to our clients and have all their data in excel. Simple enough, but I need to create a field/column that shows who in my company the email is from taking into account that in most cases each contact needs to have more than one person appearing in the “from” field/column.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’ve sorted the data by contact, so now many contacts are on multiple lines and I need to merge people in the “from” column into one cell for that owner and separate them with a comma and then delete the duplicate rows.

For example:


<o></o>
<o></o>

Any help would be great!<o></o>
Matt<o></o>

 

[taigovinda]

Welcome to the board!

I'm sure there are many more elegant ways to do this, but here's a quick way (first sort your data by email address if it's not already):

Excel Workbook
	A	B	C	D	E	F
1	First Name	Last Name	Email	From	Concat	RevSort
2	John	Doe	john@doe.com	Peter	Peter	1
3	John	Doe	john@doe.com	Jane	Peter, Jane	2
4	John	Doe	john@doe.com	Paul	Peter, Jane, Paul	3
5	John	Doe	john@doe.com	Sarah	Peter, Jane, Paul, Sarah	4
6	Tai	Govinda	tai@govinda.com	Peter	Peter	5
7	Tai	Govinda	tai@govinda.com	Jane	Peter, Jane	6
8	Tai	Govinda	tai@govinda.com	Paul	Peter, Jane, Paul	7
9	Tai	Govinda	tai@govinda.com	Sarah	Peter, Jane, Paul, Sarah	8
Sheet1

Excel 2003

Cell Formulas
Range	Formula
E2	=IF(C2=C1,E1&", "&D2,D2)

...Paste down the formula in column E, then paste values over the result. Then sort descending by column F and use "Remove Duplicates" from the Data ribbon in order to remove all but the top-most instance of each email address (within Remove Duplicates, check only Email Address).

 

[parfm]

Thanks for this! You've saved someone a lot of copy/pasting as possible madness!

 

[MickG]

Hi, An Alternative, Results start "F2".

[COLOR=navy]Sub[/COLOR] MG14Dec46
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Twn [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Q
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count, 1 To 4)
        [COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        Twn = Dn & Dn.Next
            [COLOR=navy]If[/COLOR] Not .Exists(Twn) [COLOR=navy]Then[/COLOR]
                n = n + 1
                .Add Twn, Array(n, 1)
                ray(n, 1) = Dn: ray(n, 2) = Dn.Item(1, 2)
                ray(n, 3) = Dn.Item(1, 3): ray(n, 4) = Dn.Item(1, 4) & ","
            [COLOR=navy]Else[/COLOR]
                Q = .Item(Twn)
                Q(1) = Q(1) + 1
                ray(Q(0), 4) = ray(Q(0), 4) & Dn.Item(1, 4) & ","
                .Item(Twn) = Q
[COLOR=navy]           End[/COLOR] If
[COLOR=navy]   Next[/COLOR]
Range("F2").Resize(.Count, 4) = ray
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Chris C]

Sorry to resurrect an old thread guys but I have a similar scenario which I am hoping you can help me with, see below:


[TABLE="width: 661"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 3"]CURRENT STATE OF DATA:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Emails[/TD]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]johndoe@abc.com[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]johndoe@abc.com[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]johndoe@abc.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Desired Data:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Emails[/TD]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]johndoe@abc.com[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]