Create Unique list based on 2 columns values

[JonasTiger]

Hi
I would aprecciate help in formula to create unique list based on 2 columns values:
Tryng with "=SORT(UNIQUE(A3:A26;FALSE))"
Thanks

Col A	Col B		Col D	Col E
EXISTING LIST			RESULT WANTED
Value 1	Value 2		Value 1	Value 2
123	ABC		123	ABC
123	ABC		123	DEF
123	DEF		123	GHI
123	GHI		123	JKL
123	GHI		456	ABC
123	JKL		456	DEF
123	JKL		456	GHI
456	ABC		456	JKL
456	DEF		456	MNO
456	GHI		789	ABC
456	GHI		789	DEF
456	JKL		789	GHI
456	MNO		789	JKL
456	MNO		789	MNO
456	MNO		789	PQR
789	ABC
789	DEF
789	DEF
789	GHI
789	JKL
789	JKL
789	MNO
789	PQR

 

[Fluff]

Change the A3:A26 to A3:B26

 

[JonasTiger]

Hi
In fact, it works in consecutive columns. I've tried that already.
I'm afraid I wasn't clear enough. My mistake, sorry.
Cols in existing list are not consecutive. For example, Value 1 is in Col A, Value 2 is in Col C. In this option, formula doesn't work:
I've tried "=SORT(UNIQUE(A3:A26;C3:C26;FALSE))" or "=SORT(UNIQUE((A3:A26)*(C3:C26);FALSE))" - no success

 

[mohadin]

Hi
Try

Sub test()
    Dim a As Variant
    Dim i As Long
    With Sheets("sheet1")
    a = .Range(.Cells(5, 1), .Cells(5, 1).End(xlDown)).Resize(, 3)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If Not .exists(a(i, 1) & a(i, 3)) Then .Add (a(i, 1) & a(i, 3)), Array(a(i, 1), a(i, 3))
        Next
        a = Application.Index(.items, 0, 0)
         End With
       .Cells(5, 5).Resize(UBound(a), 2) = a
    End With
End Sub

 

[Fluff]

Ok,how about

=SORT(UNIQUE(index(A3:C26;SEQUENCE(rows(A3:A26));{1\3});FALSE))

 

[JonasTiger]

Hi
Thank you for your suggestion, I will try.
But, any options in formulae?
@Fluff
your message entered at same time. I will go try your formula

 

[JonasTiger]

Ok,how about

=SORT(UNIQUE(index(A3:C26;SEQUENCE(rows(A3:A26));{1\3});FALSE))

Thank you very much. It works perfect

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JonasTiger]

Hi
Try

Sub test()
    Dim a As Variant
    Dim i As Long
    With Sheets("sheet1")
    a = .Range(.Cells(5, 1), .Cells(5, 1).End(xlDown)).Resize(, 3)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If Not .exists(a(i, 1) & a(i, 3)) Then .Add (a(i, 1) & a(i, 3)), Array(a(i, 1), a(i, 3))
        Next
        a = Application.Index(.items, 0, 0)
         End With
       .Cells(5, 5).Resize(UBound(a), 2) = a
    End With
End Sub

Thank you for your contribution, I will try later and compare performance with @Fluff formula

 

[Fluff]

The formula should be faster, especially on large data sets.