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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Need to enter the current time into a cell? It's easy to do using this keyboard shortcut. The shortcut is a handy one to ...
Discover MoreHave you ever typed something in Excel, only to have it replace whatever is to the right of the insertion point? That's ...
Discover MoreWant to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy ...
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 © 2023 Sharon Parq Associates, Inc.
Comments