Remove duplicates based on value on another column

[uc]

Below is the sample data

ID	DOB	Ethnicity	Email
1	01/01/2018	British or Mixed	aa.aol.com
1	01/01/2018	Other Mixed
2	02/05/2015	British or Mixed British
2	02/05/2015	Mixed British	xx.bt.com
2	02/05/2015	British or Mixed British	xx.bt.com
3	01/06/2012	Other Mixed	ww.bt.com
3	01/06/2012		ww.bt.com
4	01/01/2011	White and Asian
4	01/01/2011	White and Asian

I want the output as

ID	DOB	Ethnicity	Email
1	01/01/2018	British or Mixed	aa.aol.com
2	02/05/2015	Mixed British	xx.bt.com
3	01/06/2012	Other Mixed	ww.bt.com
4	01/01/2011	White and Asian

I have a table with duplicates and once I remove the duplicates I want to make sure records that have email addr is not deleted. any advise is greatly appreciated.

[BA_Pete · Accepted solution]

Hi @uc ,

Multi-select (Ctrl+click) [ID] and [DOB].

On the Home tab, go to Group By.

In the aggregate columns section at the bottom, create two aggregate columns as follows:

New column name: Ethnicity

Operation: Max

Column: Ethnicity

New column name: Email

Operation: Max

Column: Email

This gives me the following output:

Pete

[uc]

It works!! Thanks a lot 🙂