Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Deleting Duplicate Columns.

Deleting Duplicate Columns

Written by Allen Wyatt (last updated October 20, 2018)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


5

Dror has a worksheet that contains quite a bit of data. It is possible that the data in one column will be exactly the same as the data in another column, so he wonders if there is an easy way to delete any duplicate columns within the worksheet.

The first step, of course, is to figure out if two columns are identical or not. This can be determined rather easily with an array formula such as the following:

=AND(A1:A100=B1:B100)

(Remember that an array formula is entered by using Shift+Ctrl+Enter.) The formula compares all the values in the first 100 rows of columns A and B. If they are all the same, then the formula returns TRUE. If any of the cells don't match, then the formula returns FALSE. If the result is TRUE you could then delete one of the columns because they are the same.

If you want something that is a bit more automatic, meaning that the duplicate column is deleted, then you'll need to use a macro. The following steps through all the columns in the worksheet and, starting with the right-most column, compares all the columns. If any are the same—regardless of their order in the worksheet—then the macro asks if you want the duplicate column deleted.

Sub DeleteDuplicateColumns()
    Dim rngData As Range
    Dim arr1, arr2
    Dim i As Integer, j As Integer, n As Integer

    On Error Resume Next
    Set rngData = ActiveSheet.UsedRange
    If rngData Is Nothing Then Exit Sub

    n = rngData.Columns.Count

    For i = n To 2 Step -1
        For j = i - 1 To 1 Step -1
            If WorksheetFunction.CountA(rngData.Columns(i)) <> 0 And _
              WorksheetFunction.CountA(rngData.Columns(j)) <> 0 Then
                arr1 = rngData.Columns(i)
                arr2 = rngData.Columns(j)
                If AreEqualArr(arr1, arr2) Then
                    With rngData.Columns(j)
                        'mark column to be deleted
                        .Copy
                        If MsgBox("Delete marked column?", vbYesNo) _
                          = vbYes Then
                            rngData.Columns(j).Delete
                        Else
                            'remove mark
                            Application.CutCopyMode = False
                        End If
                    End With
                End If
            End If
        Next j
    Next i

End Sub
Function AreEqualArr(arr1, arr2) As Boolean
    Dim i As Long, n As Long
    AreEqualArr = False
    For n = LBound(arr1) To UBound(arr1)
        If arr1(n, 1) <> arr2(n, 1) Then
            Exit Function
        End If
    Next n
    AreEqualArr = True
End Function

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5674) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Deleting Duplicate Columns.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Continuing Macro Lines

Program a macro, and you can easily find that some lines get very long. If you want to shorten the lines so they are more ...

Discover More

Calculating Dates with Fields

Can you calculate dates using fields? Yes, but you probably don't want to except as a learning experience. An easier way ...

Discover More

Changing the Color of Worksheet Gridlines

Want the gridlines in your worksheet to be a different color? You aren't limited to stodgy black; Excel lets you make ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Synchronizing Lists

Two lists of similar data can be challenging to synchronize. Here are some ways that you can align data in two different ...

Discover More

Understanding AutoComplete

Entering data in a worksheet can be time consuming. One of the tools that Excel provides to make entry easier is ...

Discover More

Alt+Enter Stopped Working Correctly

What do you do if a keypress you know worked correctly before all of a sudden stops working as you expect? This tip ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2018-10-22 11:01:49

Roy

@Bill

When you enter a formula, you ordinarily finish it off by pressing the Enter key, or even by clicking a cell with the mouse.

For an array formula you finish it off differently: you press the key combination Shift-Control-Enter.

If you do not, Excel does not treat it as an array formula and you will get a wrong result or perhaps an error. (It's possible to get a correct result, but only if: a) by chance, or b) due to Excel's "relative" handling of some functions. Also, the "by chance" possibility is usually only for the first cell with the array formula. If you put it in 10 cells, the other nine are probably wrong. And the second possibility only works in the rows looked at (for instance, if you have ten cells in rows 1-10 that you look at, but your array formula is in rows 4-13, only rows 4-10 might have correct results while rows 11-13 will give you an error, no matter what). And often a function is arranged differently or has dfferent options ("arguments") as an array formula than when it is not, so you can't even have chance work for you.

Since many formulas can work as array formulas, or not, you may not get an error and wonder what you did wrong: it looks perfectly all right so long as the result is not obviously impossible if it worked right (like you expect a result over 200,000 but the result you get is 43) and you go merrily on thnking you're golden though you're nothing of the kind.

Lastly, given the comment, I would also point out that looking at an array formula, you will see it looks different as well. It will have a curly brace ("{") BEFORE the "=" and another one ("}") after everything else"

=B1 NOT being treated by Excel as an array formula
{=B1} IS being treated by Excel as an array formula


2018-10-22 10:46:53

Dave Bonin

Bill,

If you are not familiar with array formulas, then I recommend you don't use them. They are easy to accidentally break, especially if your workbook is used by others who are also not familiar with array formulas.

Here's a regular formula that will work just as well:
= SUMPRODUCT( - - ( A1:A100 = B1:B100 )) = ROW( A100 ) - ROW( A1 ) + 1

Adjust the row numbers as needed. For example, if your data starts in row 5 and contains 512 values:
= SUMPRODUCT( - - ( A5:A516 = B5:B516 )) = ROW( A516 ) - ROW( A5 ) + 1

Of course, if your data starts in row 5, and it currently contains 512 values, and you add more from time-to-time, and there's nothing else below your data, then this will work too and you won't have to adjust it.
= SUMPRODUCT( - - ( A5:A1000 = B5:B1000 )) = ROW( A1000 ) - ROW( A5 ) + 1

Knowing the pedantic nature of some readers of these tips, keep watching for more elegant solutions.


2018-10-22 06:15:01

Bill

What do you mean, “Remember that an array formula is entered by using Shift=Ctrl+Enter.”? I don’t have a clue what you are referring to. I certainly could use this tip, so I appreciate any clarification.


2018-10-22 03:20:04

Thomas Papavasileiou

Elegant solution
Just a remark. The need of "On Error Resume Next" command is obvious, but as a best practice I think that it should be followed by "On Error GoTo 0"
as soon as possible and in this preseny case just before the "n = rngData.Columns.Count" line.
That will contribute to easily debug any programming or mismatch error that may occur in the remaning part of the macro.


2018-10-21 06:51:03

Willy Vanhaelen

The macro can be shorter by using the array formula mentioned in this tip instead of the AreEqualArr function.
This formula can be implemented in vba by usingf the Evaluate method:

If Evaluate("AND(" & RngData.Columns(i).Address _
& "=" & RngData.Columns(j).Address & ")") Then

replaces

arr1 = rngData.Columns(i)
arr2 = rngData.Columns(j)
If AreEqualArr(arr1, arr2) Then

so we don't need the AreEqualArr function anymore:

Sub DeleteDuplicateColumns()
Dim rngData As Range
Dim i As Integer, j As Integer

On Error Resume Next
Set rngData = ActiveSheet.UsedRange
If rngData Is Nothing Then Exit Sub

For i = rngData.Columns.Count To 2 Step -1
For j = i - 1 To 1 Step -1
If Application.CountA(rngData.Columns(i)) <> 0 And _
Application.CountA(rngData.Columns(j)) <> 0 Then
If Evaluate("AND(" & rngData.Columns(i).Address & _
"=" & rngData.Columns(j).Address & ")") Then
With rngData.Columns(j)
.Copy 'mark column to be deleted
If MsgBox("Delete marked column?", vbYesNo) _
= vbYes Then
rngData.Columns(j).Delete
Else 'remove mark
Application.CutCopyMode = False
End If
End With
End If
End If
Next j
Next i


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.