Remove Duplicate Values In Excel Sheet

Though Remove Duplicate Values in excel is quite easy many of us not known how to do this. Therefore facing many difficulties while remove them by manually. Here we discuss

1. Remove Duplicate command on Data Tab
2. Highlight Duplicates using Conditional formatting and
3. Macro

Remove Duplicate Values Command

This command is placed under the data tab of the excel ribbon. We can use this command to remove duplicate values across single column or many columns. However, if we use this across multiple columns, it will remove only the rows which are having How to use Remove Duplicate Command

duplicates in all columns.  As an example, see this image. We can see row 2 and row 4 same except country. Further row 3 and 5 are exactly same If we select column A, B & C then this command remove row 5 only. (It is an ideal duplicate of row 2. If we select column A only, the command will remove cell A4 & A5. Nothing changes in column B & C as we select column A only.

1. Select the range you are going to clean
2. Then goto the data tab on the Ribbon.
3. After that Click Remove Duplicates button on the Data Tools Tab
4. If you selected a single column only and if there are data on the next column, excel will ask you to expand the selection.
5. Select your Choice and then press the Remove Duplicates Button.

Highlight Duplicates Using Conditional formatting

This is with the excel versions after 2007. You can highlight the duplicate values using conditional formatting and then remove them. Using this method you can just highlight them but not delete which is an advantage if you really need to visualize duplicates only. How to Highlight Duplicate Using Conditional Formatting

1. Select the range which is having duplicate values.
   
2. Then go to the home tab.
3. After that click conditional formatting on the styles group.
4. Then Click Highlight cells > Duplicate Values
5. Now you can set the formatting for the duplicate value cells. (default is red fill with dark red font)
6. Finally if you are going to
   • Visualize only, Leave it as it is.
   • Remove Duplicate Values, delete the highlighted values one by one.

Macro to Remove Duplicate Values

We can easily record a macro for first method described above and use later. If you don't know how to record a macro, then visit our post describing that.

"How to Record a Macro by KTK Tools"

First of all start macro recording. Assign a name and a shortcut Key to the macro. Then simulate the steps described in "How to use Remove Duplicate Command" section. After that add new data set to the sheet and select the range aa you selected while recording. Then press shortcut key to the macro. You can see macro activates and remove duplicates instantly.

If you are going to save the excel file with the macro, use save as and select excel macro enabled workbook as the save as type on save as pop up.

Show Hide Scroll Bars to Make Professional Looking Excel Tools

Show hide scroll bars excel is allow you to build more professional looking excel tools without scrolling. Therefore, if you know how to show hide scroll bars in excel it is a bonus for you. Further it is a simple setting in excel and most  of users don't know about it. Now let's see how we can use this cool feature.

Show Hide Scroll Bars in Excel

• Click on the File
• Then Click on the Options
• After that Select the Advance on the left side selections
• Finally scroll down to Display options for this workbook

Now you can see there are two tick boxes named Show horizontal scroll bar & Show vertical scroll bar as above picture. They are ticked by default and remove the tick to hide scroll bars. If you change you your mind you can tick them again to show the scroll bars.

There is a small video to demonstrate this and also how to enable disable sheet navigation. You can check it here.

Clean / Filter Set of Data Using a Keyword(s) - Part 02

If you are interested about manual method instead of VBA/Macro check my previous post.

Clean / Filter Set of Data Using a Keyword(s) - Part 01

This Code has 5 steps which will describe here.

First Step

Declaration of  variables and assigning variables

Dim a As Long, b As Long, c As Long, d As Long 

Dim i As Integer, j As Integer, k As Integer, l As Integer

Dim dl As Worksheet, da As Worksheet, wo As Worksheet, wk As Worksheet 

Dim st As Variant

Dim rn As Range

Application.EnableEvents = False

Application.ScreenUpdating = False

Set dl = Sheets("Data List")

Set da = Sheets("Dashboard")

Set wo = Sheets("List Without Keywords")

Set wk = Sheets("List With Keywords")

There are 5 sheets in this workbook. I have assigned 4 of them to variables for easy use. Then turned off events and screen updating to speed up the macro,

Second Step

Clear available data from output sheets and find last rows of input sheet and Keyword list. (Keyword list is on the column A of the Dashboard Sheet.

a = dl.Cells(Rows.Count, "A").End(xlUp).Row

i = dl.Cells(1, Columns.Count).End(xlToLeft).Column

j = da.Cells(Rows.Count, "A").End(xlUp).Row

wo.Cells.Clear

wk.Cells.Clear

dl.Activate

dl.Range("A1", Cells(1, i)).Copy Destination:=wo.Range("A1")

dl.Range("A1", Cells(1, i)).Copy Destination:=wk.Range("A1")

b = 2

l = Range(da.Range("B2").Value & 1).Column

variable b is going to use as row number of first row which is blank in the sheet List With Keywords sheet. Initially it is 2 

Step Three

Loop through every cell in keyword list, filter data set using the each keyword and copy data with keyword to the sheet List With Keywords. Finally visible cells in the Data List sheet colored using yellow.

For k = 2 To j

st = da.Cells(k, "A").Value

dl.Range("A1", Cells(a, i)).AutoFilter Field:=l, Criteria1:="=*" & st & "*", Operator:=xlAnd

Set rn = Nothing

On Error Resume Next

Set rn = dl.Range("A2", Cells(a, i)).SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If Not rn Is Nothing Then

rn.Copy Destination:=wk.Range("A" & b)

b = b + rn.Cells.Count / i

rn.Interior.Color = RGB(255, 255, 0)

End If

Next

During every iterate of the loop b recalculating to have last empty row number. Here we can't use rn.Rows.Count because rn is not a continuous range. Some rows are hidden according to the filter and therfore rn.Rows.Count give the count of first continuous rows which is not correct. To overcome this error this code calculate all cells in the range and divide it by number of columns. (Cells Count = Columns Count * Rows Count)

Step Four

Filter the Data List sheet to show all rows which are not having any color and copy those data to the sheet List Without Keywords. These are the subset of Data List sheet data which don't having any keyword.

dl.Range("A1", Cells(a, i)).AutoFilter Field:=l, Operator:=xlFilterNoFill

Set rn = Nothing

On Error Resume Next

Set rn = dl.Range("A2", Cells(a, i)).SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If Not rn Is Nothing Then

rn.Copy Destination:=wo.Range("A2")

End If

dl.AutoFilterMode = False

dl.Cells.Interior.Pattern = xlNone

After all filter in the sheet Data List is removed using AutoFilterMode=False and all colors add to the cells set to none.

Step Five

If one row in Data List sheet included more than one keyword, macro copied it more than one to the List With Keywords sheet. Step Five is to remove those duplicates. If there is more than one row to consider .RemoveDuplicates method need to have all column numbers as a array. First four lines of this part is to build that array.

ReDim st(0 To i - 1)

For a = 0 To UBound(st)

st(a) = a + 1

Next

wk.Activate

wk.Range("A2", Cells(a, i)).RemoveDuplicates Columns:=(st), Header:=xlNo

Step Six

Finally code will display the Dashboard sheet and enable Events and screen updates.

da.Activate

MsgBox "Filter Complete", vbInformation

Application.ScreenUpdating = True

Application.EnableEvents = True

During this part code notify the user that process is over. If you turned off events and screen updating during any macro, make sure to turned on within the macro, Otherwise they are turned of until you close all opened excel files and open excel.

You can download the sample excel sheet with the macro using below link

Clean Data List.xlsm