Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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

by Allen Wyatt
(last updated July 14, 2017)

9

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

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5674) applies to Microsoft Excel 2007, 2010, and 2013. 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

Creating an Inline Heading

When settling on an overall design for your document, you need to decide how you want your headings to appear. If you want ...

Discover More

Creating an E-mail Message from the Current Document

In some earlier versions of Word you could type an e-mail in your document and then have Word e-mail it, as a message, to ...

Discover More

Sorting Dates by Month

Sorting by dates is easy, and you end up with a list that is in chronological order. However, things become a bit more tricky ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Three-Dimensional Transpositions

Excel makes it easy to transpose your data so that rows become columns and columns rows. It doesn't have a built-in ...

Discover More

Using AutoComplete with Disjointed Lists

AutoComplete can help you to more quickly enter information in a worksheet. How it works, behind the scenes, can affect how ...

Discover More

Copying Cells to Fill a Range

Excel provides two really helpful shortcuts you can use to fill a range of cells, either horizontally or vertically. These ...

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. 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 7 - 0?

2017-07-21 04:43:31

Willy Vanhaelen

@Dave Bonin
Indeed if you produce workbooks to use by so many people you have a point.
I am the only user of the workbooks I create so I am free to do what I like :-)


2017-07-17 10:30:57

Dave Bonin

@Willy Vanhalen

Unfortunately, the average, everyday user of Excel is not familiar with array formulae,
much less the Ctrl+Shift+Enter sequence used to complete an array formula.

This leads to issues when users "examine" a formula and then inadvertently re-enter
the formula without the Ctrl+Shift+Enter sequence. The newly-non-array formula
no longer produces the desired result and computational pandemonium ensues.

I produce workbooks to use as tools in our little 95,000 employee company. Once
released to the wild, I never know who might choose to adapt my workbook to suit
some special, parochial purpose. Many of the adopting adapters are not familiar
with array formulae. This leads to much frustration and taking of my name in vain
through no fault of my own.

The SUMPRODUCT() function, while acting much like an array formula, has no
special entry syntax. In that way, by mimicking a normal formula, they are much
safer for use around typical users.

In my humble opinion, Microsoft did a disservice to users when it introduced and
required the special array formula entry sequence, especially while doing nothing
much further to aid the user to identify an array formula. The brackets, while
helpful, are insufficient.

While working with array formulae, it's very easy to forget to Ctrl+Shift+Enter.
I've done it countless times and I suspect you have, too. If we're really good
with Excel and we mess up, imagine how much harder it is for more average
users.


2017-07-15 09:28:04

Willy Vanhaelen

@Dave Binin
When the steam engine was invented one proclaimed that the cows would't give no milk anymore :-). If you detest array formulae simply don't use them.

I use them a lot for many years now and had no problem with them at all. They are very robust, extremely reliable, usefull and efficient, but of course you must understand there working and know how to use them.

BTW: the SUMPRODUCT() function is in fact an array formula "in disguise".


2017-07-14 10:59:39

Dave Bonin

I detest array formulae. They are fragile, fussy creatures, prone to birth defects
and premature mortality whenever anyone looks at them the wrong way --
particularly your average, everyday pedestrian user.

Instead, I highly recommend using the far more robust SUMPRODUCT() function.


2013-07-22 14:40:24

Glenn Case

Thanks, Mike, that cleared it up for me.


2013-07-20 19:14:26

Michael (micky) Avidan

Please ignore my TWO previous posts.
(too tired at this hour: UTC+02:00)

It must be an Array Formula where ever typed.

Without the AND it returns only an array of 100 TRUE/FALSE and therefor the AND mut be there in order to return TRUE only if all 100 Cells are identiacal.

Michael Avidan
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-07-20 18:42:44

Michael (micky) Avidan

Sorry about the "TIPO".

It should read: A1:A100=B1:B100

Michael Avidan
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-07-20 18:41:01

Michael (micky) Avidan

@Glenn Case,

You are, absolutely, right about the superfluous "AND".

However - you can use a regular formula (non Array):

A1:A100=B1=B100

only if it is typed within any cell but between rows 1-100.

If you want to type the formula in cell C101 - it must be an "Array formula".

Michael Avidan
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-07-19 12:05:29

Glenn Case

I'm curiouis about the syntax of the array formula. Why is AND necessary to make this work? There's only one condition. What does AND do here?


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.