Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Deleting Duplicate Columns.
Written by Allen Wyatt (last updated May 9, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7164) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deleting Duplicate Columns.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Excel has a great (and little known) shortcut for filling a column with information. It comes in very handy when you need ...
Discover MoreExcel provides the ability to define names that refer to cells or ranges of cells. These can then be used in your ...
Discover MorePaste some information into a worksheet and Excel helpfully displays some options related t the paste operation. If you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-03-26 21:50:52
dt
Do you have a version of this that will do the same for rows? No headers and use the active selection?
Very Respectfully,
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments