I have data in columns AB&C
To find duplicate rows I concatenated the 3 cells in each row in 1 cell so now column D is occupied and duplicates of column D's cells denotes the duplicate rows.
I want to keep 1 copy and clear the other duplicates (not delete rows but clear rows)
I have been using =IF(COUNTIF($D$2:$D$1048576,$D2)>1,"x","")
Which states the range to check against, and the single cell; if it is a duplicate show x otherwise be blank.
Unfortunately there is a character limit of 250 to which the above formula then shows VALUE! This is not good enough for my needs.
I need a helper column to mark the duplicates (so i can then "go to special" do shift left to select the whole rows) to right click and clear the relevant rows or a VBA solution that uses a helper column or not to achieve this goal.
To find duplicate rows I concatenated the 3 cells in each row in 1 cell so now column D is occupied and duplicates of column D's cells denotes the duplicate rows.
I want to keep 1 copy and clear the other duplicates (not delete rows but clear rows)
I have been using =IF(COUNTIF($D$2:$D$1048576,$D2)>1,"x","")
Which states the range to check against, and the single cell; if it is a duplicate show x otherwise be blank.
Unfortunately there is a character limit of 250 to which the above formula then shows VALUE! This is not good enough for my needs.
I need a helper column to mark the duplicates (so i can then "go to special" do shift left to select the whole rows) to right click and clear the relevant rows or a VBA solution that uses a helper column or not to achieve this goal.