Forum Discussion
How do I choose which row to keep in Remove Duplicates?
Hi,
This is can be done by using a formula in a helper column to identify the columns that you need to delete.
I suggest this formula:
=IF(COUNTIF($B$2:$B$10,B2)>1,IF(A2=0,"To be removed",""),"")
After that, you can sort the helper column from A to Z, then select the marked columns and delete them.
Hope that helps
Hi,
This is can be done by using a formula in a helper column to identify the columns that you need to delete.
I suggest this formula:
=IF(COUNTIF($B$2:$B$10,B2)>1,IF(A2=0,"To be removed",""),"")
After that, you can sort the helper column from A to Z, then select the marked columns and delete them.
Hope that helps
Haytham Amairah Thank you for this information! This solves part of my issue. I have an ID in Column A (some unique and some duplicates), Version number in column B (blank is the first version, then version 1, version 2, etc.) and Amount in column C. I want to add the Amount in Column C for the highest Version (column B) for each ID (column A) only once.
Also, it is possible that there may be more than one ID with the same version with different amounts. Could these be flagged for human review to determine which one to sum e.g. #Error if they are the highest Version?
- It's impossible that this answer can be bettered.
Thank you so much for taking the time to answer. It's greatly appreciated.
