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

[martaTr]

hi, my table is created by selected columns and union, so i cant use PQ, do you know any other solution for the same problem?

[dufoq3]

Hi @martaTr, could you provide sample data in usable format (read note below my post if you don't know how) and expected result based on sample data?

[uc]

It works!! Thanks a lot 🙂