Remove Duplicate Rows - Duplicates scattered over different columns in different rows

Question

Let's say I have table like:

first row: 111 | A | 222 | B

second row: 222 | B | 111 | A

And I just would like to keep one of those rows, how to do this?

Answer 1

In Google Sheets, you can add a "helper column" with the formula

=join("|",sort(TRANSPOSE(table_row_reference)))

and then use the Remove Duplicates tool

In Excel O365, you can do the same except the formula would be:

=TEXTJOIN("|",,SORT(table_row_reference,,,TRUE))

Note: table_row_reference is a reference to a single row in the table, eg A1:D1

In earlier versions of Excel (Excel 2010+) you can use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

        //Change all columns to type Text
        typed = Table.TransformColumnTypes(Source,
                    List.Transform(
                        Table.ColumnNames(Source),
                        each {_, type text}
                    )
                ),
    #"Added Custom" = Table.AddColumn(typed, "Merged", each Text.Combine(List.Sort({[Column1],[Column2],[Column3],[Column4]}),"|")),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Merged"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Merged"})
in
    #"Removed Columns"

Answer 2

If you can use a helper column to find duplicates in column B to I from row number 2 then enter the following formula in the row two of the helper column.

=Sort_and_CONCATENATE(B2:I2,"|")

and copy down the formula.

This formula will sort and merge cells in the range "B2:I2". Then you can use remove duplicates on this column.

Following is the user defined function code in VBA

Option Explicit
'============================================================================
Function Sort_and_CONCATENATE(myRng As Range, deLmt As String, Optional srtCriteria = 0)
'srtCriteria is criteria to sort; 0 or nothing for Ascending, Other digit for descending.
Dim myString As String, Lb As Long, Ub As Long, i As Long, j As Long
Dim arr, reverseArray
Dim strTemp As String

arr = myRng.Value
myString = Join(Application.Index(arr, 1, 0), deLmt)
arr = Split(Trim(myString), deLmt)
Lb = LBound(arr)
Ub = UBound(arr)

For i = Lb To Ub - 1
    For j = i + 1 To Ub
        If IsNumeric(arr(i)) = True And IsNumeric(arr(j)) = True Then
            If Val(arr(i)) > Val(arr(j)) Then
            strTemp = arr(i)
            arr(i) = arr(j)
            arr(j) = strTemp
            End If
        Else
            If IsDate(arr(i)) = True And IsDate(arr(j)) = True Then
                If DateValue(arr(i)) > DateValue(arr(j)) Then
                strTemp = arr(i)
                arr(i) = arr(j)
                arr(j) = strTemp
                End If
            Else
                If (arr(i)) > (arr(j)) Then
                strTemp = arr(i)
                arr(i) = arr(j)
                arr(j) = strTemp
                End If
            End If
        End If
    Next j
Next i

If srtCriteria = 0 Then
    Sort_and_CONCATENATE = Join(arr, deLmt)
    Else
    ReDim reverseArray(Ub)
        For i = 0 To Ub
            reverseArray(i) = arr(Ub - i)
        Next
    Sort_and_CONCATENATE = Join(reverseArray, deLmt)
End If

End Function

Also, using this function, we can create following procedure to remove duplicates. Changes in Excel caused by macro cannot be undone. Please try macros on sample/ duplicate data. If we use following procedure, we don't have to use helper column.

Option Explicit
'============================================================================
Sub removeDuplicatesAcrossColumns()
Dim resultArr(), resultColl As New Collection, tblRng As Range, i As Long, j As Long
Set tblRng = Application.InputBox("Select Table Range", "Table Range", , , , , , 8)

For i = 1 To tblRng.Rows.Count
    If ExistsInCollection(resultColl, Sort_and_CONCATENATE(tblRng.Rows(i), "|")) = False Then
        resultColl.Add i, Sort_and_CONCATENATE(tblRng.Rows(i), "|")
        ReDim Preserve resultArr(1 To tblRng.Columns.Count, 1 To resultColl.Count)
        For j = 1 To tblRng.Columns.Count
            resultArr(j, resultColl.Count) = tblRng(i, j).Formula
        Next
    End If
Next
tblRng.ClearContents
Range(tblRng(1, 1).Address).Resize(UBound(resultArr, 2), tblRng.Columns.Count).Formula = Application.Transpose(resultArr)

End Sub
'============================================================================
Public Function ExistsInCollection(col As Collection, key As Variant) As Boolean
    On Error GoTo err
    ExistsInCollection = True
    IsObject (col.Item(key))
    Exit Function
err:
    ExistsInCollection = False
End Function
'============================================================================

In the GIF below, helper column is used to show duplicates. Otherwise, no helper column is required in case of above procedure.