Combine duplicate rows with text in columns

问题:

I have data that looks like this:

Column A contains multiple duplicate data. I need a formula/solution that will remove duplicate email addresses in column A, but combine data in columns B, C and D. In the end, I need it to look like this:

回答1:

If you want a formula, starting in G2 enter this with CTRL + SHIFT + ENTER and copy down:

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($G$2:G2))),"")

In cell H2 enter this also with CTRL + SHIFT + ENTER and copy down and across:

=IFERROR(LOOKUP("ZZZ",IF($G2=$A$2:$A$7,B$2:B$7)),"")

回答2:

1. if you go to the Data tab located in the top navigation, then go to Data tools (notice the headings at the bottom), after that click the column header that you want to remove duplicates, then click Remove duplicates.
2. To help duplicates stand out; conditional formatting in the home tab, then select quick styles -> duplicate values.
3. The data tab, you can use advanced (under Sort & Filter) formatting to only contain unique values.

Hope this helps.