Unique Value from combination of 2 columns

[vpranitha]

Hello all,

I have 2 columns like below

Obligor_ID	Facility_ID
111111	wsd
23455	ed
111111	eff
56765	ed
111111	wsd
8888888	td

<tbody>
</tbody>

<tbody>
</tbody>

Looking to create a new table only capturing unique values, where the Obligor ID and Facility ID are unique. In the above example should only have 5 rows. The formula is going to be used for 10,000 rows of data.

Appreciate the groups assistance with this.

Thank you

 

[Marcelo Branco]

Try Advanced Filter, Copy to another location
check Unique records only

M.

 

[YasserKhalil]

May be

Sub Test()    Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=VBA.Array(1, 2), Header:=xlYes
End Sub

 

[YasserKhalil]

Another approach

Sub UniqueFromTwoColumns()
    Dim i           As Long
    Dim arr         As Variant
    Dim e          As Variant


    arr = ActiveSheet.Range("A1").CurrentRegion


    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 2 To UBound(arr)
            .Item(arr(i, 1) & "," & arr(i, 2)) = .Item(arr(i, 1) & "," & arr(i, 2))
        Next i


        i = 2
        Range("E1:F1").Value = Array("Obligor_ID", "Facility_ID")
        For Each e In .keys
            Cells(i, "E").Resize(, 2) = Split(e, ",")
            i = i + 1
        Next
    End With
End Sub

 

[seekerarcane]

Please do the followings;

1. Join or concatenate values of both columns in a third column with a delimiter, like ","

2. Remove the duplicate values from this third column

3. Applying "Text to Column" feature, reverse the concatenation of unique values and place them in separate columns again.


You will achieve this without any formula.

 

[vpranitha]

Thank you