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

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, and 2016. You can find a version of this tip for the older menu interface of Excel here: **Deleting Duplicate Columns**.

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

Need to quickly select a range of cells? Perhaps the easiest way is to use both the mouse and the keyboard together, as ...

Discover MoreNeed to edit the data within a cell? There are any number of ways you can perform the edit; this tip documents them all.

Discover MoreIf you copy a cell that contains a reference to external data, do you get an error? It could be due to the complexity of ...

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

2017-07-21 04:43:31

Willy Vanhaelen

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

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

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

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

(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

It should read: A1:A100=B1:B100

Michael Avidan

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

ISRAEL

2013-07-20 18:41:01

Michael (micky) Avidan

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

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.

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

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments