How to remove repeating elements from two columns in Excel

Related searches

I have two columns for example:

A      B

1      2

1      3

1      5

3      4

2      1

4      3

I want the output to come like this:

A      B

1      2

1      3

1      5

3      4

I want to remove the data which gets repeated from 1 column to another. Is there any function in excel to do so?

The following code will load the Range into an Array. It will then go through each Row of the Array in turn, sort the values in the Row, and compare that against already-processed rows.

If it is new, it is added to the Output and Processed Rows - if it already exists then it is ignored. Finally, it will output the array back into the original Range

This is slightly more complicated than it needs to be, to preserve the order of elements in the first row for each combination - a simpler method would have been to sort the columns of each row into order and then just use "Remove Duplicates" on the whole thing.

The ArrayDimension code is nabbed from here, so please consider wandering over to upvote Emeka Eya

Sub RemoveDuplicateRows(Target As Range, Optional Permutations As Boolean = False)
'Target: Range to remove duplicate rows from
'Permutations: If FALSE then ignore the order of elements in the row
    Dim InputArray As Variant, ArrayPointer As Long

    If Permutations Then
        'This is just a normal RemoveDuplicates
        ReDim InputArray(0 To (Target.Columns.Count - 1))

        For ArrayPointer = 1 To Target.Columns.Count
            InputArray(ArrayPointer - 1) = ArrayPointer
        Next ArrayPointer

        Target.RemoveDuplicates Columns:=InputArray, Header:=xlNo
    Else
        Dim RowArray As Variant, ArrayBinding As Long
        Dim OutputArray As Variant, OutputRow As Variant
        Dim ProcessedRows As Variant, CurrentRow As String
        Dim TransferColumn As Long

        InputArray = Target.Value
        ArrayBinding = LBound(InputArray, 1)
        OutputRow = ArrayBinding

        'Empty array the same size as the input, and a 1D array the same height
        ReDim OutputArray(LBound(InputArray, 1) To UBound(InputArray, 1), LBound(InputArray, 1) To UBound(InputArray, 1))
        ReDim ProcessedRows(LBound(InputArray, 1) To UBound(InputArray, 1))

        For ArrayPointer = ArrayBinding To UBound(InputArray, 1)
            'Get 1D array containing just this specific row
            RowArray = Application.Transpose(Application.Transpose(Application.Index(InputArray, ArrayPointer + 1 - ArrayBinding, 0)))

            'Sort the Array and Flatten into a string for Searching
            CurrentRow = SortArrayAndFlatten(RowArray)

            'If the row is new, then add it
            If UBound(Filter(ProcessedRows, CurrentRow)) < 0 Then
                For TransferColumn = LBound(RowArray) To UBound(RowArray)
                    OutputArray(OutputRow, TransferColumn) = RowArray(TransferColumn)
                Next TransferColumn
                'Mark the row as already processed
                ProcessedRows(OutputRow) = CurrentRow
                OutputRow = OutputRow + 1
            End If
        Next ArrayPointer

        Target.Clear
        Target.Value = OutputArray
    End If
End Sub

Private Function SortArrayAndFlatten(ByVal TargetArray As Variant) As String
'This will output a String of the Sorted Elements of the Array
    If Not IsArray(TargetArray) Then Exit Function
'Only work on 1D arrays
    If ArrayDimension(TargetArray) > 1 Then Exit Function

    Dim OuterLoop As Long, InnerLoop As Long, StoppingPoint As Long, HoldingBucket As Variant, NoSwaps As Boolean
    StoppingPoint = LBound(TargetArray) + 1
    For OuterLoop = UBound(TargetArray) To StoppingPoint Step -1
        NoSwaps = True
        For InnerLoop = OuterLoop To StoppingPoint Step -1
            If TargetArray(InnerLoop) > TargetArray(InnerLoop - 1) Then
                'Swap the elements
                HoldingBucket = TargetArray(InnerLoop)
                TargetArray(InnerLoop) = TargetArray(InnerLoop - 1)
                TargetArray(InnerLoop - 1) = HoldingBucket
                NoSwaps = False
            End If
        Next InnerLoop
        If NoSwaps Then Exit For
    Next OuterLoop
    SortArrayAndFlatten = Join(TargetArray, "|")
End Function

Function ArrayDimension(ByRef ArrayX As Variant) As Long
    Dim i As Long, a As String, arDim As Long
    On Error Resume Next
    i = 0
    Do
        a = CStr(ArrayX(0, i))
        If Err.Number > 0 Then
            arDim = i
            On Error GoTo 0
            Exit Do
        Else
             i = i + 1
        End If
    Loop
    If arDim = 0 Then arDim = 1
    ArrayDimension = arDim
End Function

Compare two columns and remove duplicates in Excel, The following code will load the Range into an Array. It will then go through each Row of the Array in turn, sort the values in the Row, and� Select all cells in Column A starting from cell A1 up to the last cell that contains data. Go to the Data tab and click Sort A to Z. In the dialog window that opens, choose " Continue with the current selection " and click Sort: Delete the column containing the formula because you do not need it any longer, only "Uniques" are left there by now.

This may helps you

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long, j As Long
    Dim strA_I As String, strB_I As String, strA_J As String, strB_J As String, strDelete As String
    Dim varDelete As Variant

    strDelete = ""

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 1 To LastRow

            strA_I = .Range("A" & i).Value
            strB_I = .Range("B" & i).Value

            For j = i + 1 To LastRow

                strA_J = .Range("A" & j).Value
                strB_J = .Range("B" & j).Value

                If ((strA_I = strA_J) Or (strA_I = strB_J)) And ((strB_I = strA_J) Or (strB_I = strB_J)) Then

                    If strDelete = "" Then
                        strDelete = j
                    Else
                        strDelete = strDelete & "," & j
                    End If

                End If

            Next j

        Next i

        varDelete = Split(strDelete, ",")

        For i = LastRow To 1 Step -1

            For j = LBound(varDelete) To UBound(varDelete)

                If i = varDelete(j) Then

                    Rows(i).EntireRow.Delete
                    Exit For

                End If

            Next j

        Next i

    End With

End Sub

7 Ways To Find And Remove Duplicate Values In Microsoft Excel , Well, I'm assuming it removes duplicates sequentially: copy the 8000 below the 500 and then use the remove duplicates function for that single� Select the range of cells that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates. Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.

This is actually pretty easy to do, but.... There's a minor issue, we don't have a complete definition of the problem.

"I want to remove the data which gets repeated from 1 column to another."

What happens when left and right columns are identical? This is presently undefined.


Start from the bottom row and concatenate the left and right column with a delimeter. If desired, test them to see if left and right are identical to each other and figure out what to do if they are; do you keep one? Toss both?. If kept, this string becomes the first element in an expanding array.

If you kept the first pair then you'll keep this pair as well, so swap the columns to build the reverse string and put it in the second element of the array.

Move up to the next row. Test for equivalency if desired, build your string. Only add the string to the array if it is a unique.

Create the reverse string, and add it to the array if unique.

Delete the row if either string exists in the array.

Repeat all the way to the top.

How to remove repeating elements from two columns in Excel , If you have two columns/lists including some duplicates, now you want to compare them Compare two columns and delete matches with Kutools for Excel be same if you check this option), and the check Each row and Same Values option. 2. Repeat above step to copy and paste all sheet contents into one sheet. 3. Select the combined contents, click Data > Remove Duplicates. 4. In the Remove Duplicates dialog, check or uncheck My data has headers as you need, keep all columns in your selection checked.

If one has the Dyanmic Array formula UNIQUE then use this:

=TRIM(MID(SUBSTITUTE(UNIQUE(IF($A$1:$A$6<$B$1:$B$6,$A$1:$A$6,$B$1:$B$6)&"|"&IF($A$1:$A$6>=$B$1:$B$6,$A$1:$A$6,$B$1:$B$6)),"|",REPT(" ",999)),(COLUMN(A1)-1)*999+1,999))

Put that in the first output cell and drag over one column.

Removing duplicates over two columns in Excel, Extract duplicates from one column with Kutools for Excel Formulas may be difficult to remember to use, but if you have Kutools for Excel’s Select Duplicate & Unique Cells, you can quickly select the duplicate cells and then copy and paste them to another column. Kutools for Excel,with more than 300 handy functions, makes your jobs more easier.

How to compare two columns and delete matches in Excel?, 2. How to Use IF Function Find Duplicates in Two Columns . Step 1: Create a Helper Column, and type the formula =IF(A2=B2,"Duplicate","No Duplicate") in the first cell; Step 2: Copy down to the bottom, the rows with duplicates in column A and column B will display "Duplicate" and non-duplicates will display "No Duplicate".

Find And Remove Duplicate Values With Power Query. Power Query is all about data transformation, so you can be sure it has the ability to find and remove duplicate values. Select the table of values which you want to remove duplicates from go to the Data tab choose a From Table/Range query. Remove Duplicates Based On One Or More Columns

Comments
  • By using the "remove duplicates" button?? support.office.com/en-us/article/…
  • Include your attempt/research where you got stuck. @B001ᛦ, that's not going to work in this case unfortunately
  • that's not going to work in this case... Why not? Because of having duplicates in 2 columns? @JvdV
  • @B001ᛦ, have you tried it =)?. Simply using remove duplicates over these two column generate zero duplicates to remove.
  • @B001ᛦ Because OP's not asking to remove the duplicates per column - for which you just run "Remove Duplicates" on each column and click "Continue with the current selection" instead of "Expand the selection" - but rather to remove all duplicates pairs, regardless of order - see how the 5th row 2 | 1 gets removed as a duplicate of the 1st row 1 | 2?