CODEWITHSUNDEEP
excelexcel-formula
Trey Copeland
Trey Copeland

Reputation: 3527

How can I remove duplicates in Excel?

I have a column with 3 of the same keywords taking up 3 rows. I need to clear the values in the first two rows in each instance.

I have highlighted the rows as an example in the screenshot:

enter image description here

Thanks for your time and help!

Upvotes: 1

Views: 250

Answers (4)

0m3r
0m3r

Reputation: 12495

Here is Function that will remove duplicates and does not shift the value up

Option Explicit
Sub ClearDuplicates()

    Dim i As Long
    Dim lRng As Long

    lRng = Range("A65536").End(xlUp).Row

    For i = lRng To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A1:A" & i), _
            Range("A" & i).Value) > 1 Then
            Range("A" & i).ClearContents
        End If
    Next i

End Sub

Example

enter image description here

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 37135

Use Remove Duplicates excel built in command. Under Data tab you will find remove duplicates command.

(1) First select desired column of range.
(2) Hit on Remove Duplicates.
(3) Follow the on screen instructions and do as necessary.

Upvotes: 0

Yash Dutt
Yash Dutt

Reputation: 197

You might want to try the below:

On the Home Tab, Click

  1. Conditional Formatting under Styles Group
  2. Highlight Cell Rules
  3. Duplicate Values

This will highlight all the duplicates in Pink(Default Color). You can then Filter by Color. Copy the unduplicated data and paste into another column.

Upvotes: 1

Wouter van Dijke
Wouter van Dijke

Reputation: 692

If you want to replace the duplicate keywords with blanks, you can make a new column to the right of your Keyword column and use an IF-function =IF(B2=B1,"",B2). If you copy that down, your original Keyword column will be copied except the duplicates will be blank. Delete duplicates with IF-function

Upvotes: 1

Related Questions