• Skip to main content
  • Skip to primary sidebar

John 3:30

He Must Increase, I Must Decrease

  • Evangelism Schedule
  • Preaching and Sermons
  • Links
    • OUTREACH GUIDELINES
    • MY TESTIMONY
    • SUPPORT THE MINISTRY
    • THE GOSPEL
    • CONTACT
  • Posts by Category
    • Open Air Preaching
    • Theology
    • Witnessing
    • Just me
    • Memory Verses
    • Creation
    • Movie Reviews
    • Love

Delete duplicates in Excel

June 15, 2010 by Michael Coughlin

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful. I have written hundreds of macros for Excel to solve basic to even quite complex tasks.

Once in a while I want to write an article on technology. Here is the first one.

Note: In Excel 2010 – this feature is built in and much more sophisticated than this script.

Have you ever had a large dataset (or small) that you imported into Excel somehow and you just want to remove all the rows that duplicate on a certain column? Here’s a couple quick macros you can use to solve your problem. Just copy and paste this code into a module in Excel VBA and run them like any other macro.

This first macro is really good if you just have a single column of data. Your focus (the currently selected cell) must be in the column of your data, and your first row of data should be in row 1. Additionally, you should not have any blanks in the dataset. If you do, just sort the data first and run this macro. It can remove a few hundred duplicates in seconds. If you are in the thousands…be patient, it can run a little while. I just ran it against a dataset with 10000 rows with only 386 unique values and it finished in just over 3 minutes. This doesn’t sound like long, but if you’re sitting there while excel is flashing, you may be tempted to end it early.

Sub DeleteDups()

    Application.CutCopyMode = False
    Dim offset, firstRow, lastRow, currCol, i As Integer
    currCol = ActiveCell.Column
    firstRow = 1
    Cells(firstRow, currCol).Select
    Selection.End(xlDown).Select
    lastRow = ActiveCell.Row
    
    Cells(firstRow, currCol).Select
    Range(Selection, Selection.End(xlDown)).Select
        
    Selection.sort Key1:=Cells(ActiveCell.Row, ActiveCell.Column), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
    offset = 0
    For i = firstRow To (lastRow - 1) Step 1
      If Cells(i - offset, currCol) = Cells(i + 1 - offset, currCol) Then
        Rows(i - offset).Delete Shift:=xlUp
        offset = offset + 1
      End If
    Next i
End Sub

The second macro is good for any situation where you have multiple columns of data. You must first SORT the data on the field you are going to remove the duplicates based upon. Your focus (the currently selected cell) must be in the column of your data we are checking for duplicates as well, and your first row of data should be in row 1. It can remove a few hundred duplicates in seconds. If you are in the thousands…be patient, it can run a little while.

Sub DeleteDupsNoSort()

    Application.CutCopyMode = False
    Dim offset, firstRow, lastRow, currCol, i As Integer
    currCol = ActiveCell.Column
    firstRow = 1
    Cells(firstRow, currCol).Select
    Selection.End(xlDown).Select
    lastRow = ActiveCell.Row
    
    Cells(firstRow, currCol).Select
    Range(Selection, Selection.End(xlDown)).Select
        
    offset = 0
    For i = firstRow To (lastRow - 1) Step 1
      If Cells(i - offset, currCol) = Cells(i + 1 - offset, currCol) Then
        Rows(i - offset).Delete Shift:=xlUp
        offset = offset + 1
      End If
    Next i
End Sub

As always, SAVE your work before running this macro. You may even make a copy of your data first, just in case. If I had a penny for every iteration of an infinite loop I’ve written…

If anyone has any other programming needs, especially little things you might just want to be able to do at home, like track a budget, let me know. I’ll post more or just help you one on one.

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful. I have written hundreds of macros for Excel to solve basic to even quite complex tasks.

Filed Under: Just me, Technical Tagged With: excel, programming

Primary Sidebar

My Budgeting App

You Need A Budget

The Bible Memory App I Use

The Bible Memory App - Bible Memory Verses

Recent Posts

  • ESV Preaching Bible, Black Goatskin Leather for Sale
  • More Lies and Clickbait Instead of Reporting Facts About Ohio Protests
  • Stimulating Your Thoughts About the Stimulus
  • Evangelism Schedule
  • New Podcast

Tags

2018 OSU abortion Adam apologetics attributes of God Bible catholicism Christ church Courageous Creation discipline end times evangelism Forgiveness glory gluttony God Gospel Grace Hollywood Holy humility Jesus Joy leadership logic Love Mercy Movies Ohio State Open Air Oracle people power prayer preaching pride programming Righteous savior Scripture self-control sin witchcraft

Recent Comments

  • Rusty on TTUN @ tOSU Ministry Report – Nov 24, 2018
  • A(nother) Surprising Work of God » Things Above Us on Nebraska @ tOSU Ministry Report – Nov 3, 2018
  • Mid-October 2018 Presuppositional Apologetics’ Links | The Domain for Truth on Minnesota @ tOSU Ministry Report – Oct 13, 2018
  • mcoughlin on Indiana @ tOSU Ministry Report – Oct 6, 2018
  • Jeff Mardling on Indiana @ tOSU Ministry Report – Oct 6, 2018

Categories

  • Creation
  • Gospel
  • Just me
  • Love
  • Memory Verses
  • memoryfeedmichael
  • Movie Reviews
  • Open Air Preaching
  • Prayer
  • Technical
  • Theology
  • Uncategorized
  • Witnessing
2018 © MichaelCoughlin.net

Copyright © 2023 · Things Above Us on Genesis Framework · WordPress · Log in