Forum Discussion
Merge duplicated records
Hello all,
I recently face a situation. I have two tables. One contain Account Number, Date, and address. The other contain Account Number and Collector. I know I need all the columns in both table. So, I choose to merge them using full outer (Account Number as the key).
What happened next is there are a lot of duplicated Account Numbers which shows like this:
They has the same acctnbr, and I need the Collector. How can I merge them together? (This is the one of many duplicated record, I just use this for instance. How can I merge all the duplicated records?)
Hi Anonymous ,
Assuming that you only ever have two distinct rows per account number, you can select the account number column then go to Home tab > Group By and add an aggregation column for each of the other records using MAX(eachColumn).
Pete
2 Replies
Hi Anonymous ,
Assuming that you only ever have two distinct rows per account number, you can select the account number column then go to Home tab > Group By and add an aggregation column for each of the other records using MAX(eachColumn).
Pete
Hi Anonymous,
I didn't try BA_Pete solution but in case you want to try an other proposition, here is mine:
1/ "Sort Ascending" your "AcctNbr" column
2/ "Sort Descending" your "Collector" column
3/ Select "Collector" column and under "Transform" pane, hit "Fill" then "Down"
4/ Select both "AcctNbr" and "Collector" columns
5/ In "Home" pane, select "Remove Rows" then "Remove duplicates"
It should do what you are trying to do.
I hope this will help you.
