Forum Discussion
Remove duplicates based on value on another column
Below is the sample data
| ID | DOB | Ethnicity | |
| 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 | |
| 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.
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
4 Replies
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
It works!! Thanks a lot 🙂
- 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?
