New Member
January 12, 2022
I have a large excel file with a lot of data.
I know how to delete the duplicate values but keep one value.
I need to learn another technique.
For example, a particular column repeats some data twice, some data thrice, and some data for four or five times.
I want to delete the first entry only. I don't want to delete the second, third or fourth entry. That means only the first entry or first data will be deleted, but all the second, third, fourth or the next entry will exist.
Will you please tell me how I can do this?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Hi,
You can just add a new column with this formula, assuming that your data is in range A2:A1000:
=AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(A$2:A$1000,A2)>1)
The formula will return TRUE only for those records matching both criteria: it is the first match (argument 1 in formula), duplicate count is > 1 (argument 2 in formula, to make sure there are duplicates for that item, otherwise it will highlight unique items as well)
Select and delete the rows where the result is TRUE.
1 Guest(s)