Unique list without office 365

[Sufiyan97]

I want to create a unique list of names from two different columns

A and B are source and expected result is in column D

Book6
	A	B	C	D	E
1	List1	List2		Unique
2	Dave	Dave		Dave
3	Dave	Dave		Dwayne
4	Dwayne	Dave		Joy
5	Dwayne	Dwayne		Amy
6	Dwayne	Joy		Ronny
7	Dwayne	Joy		Cary
8	Joy	Joy
9	Joy	Joy
10	Joy	Joy
11	Amy	Ronny
12	Amy	Cary
13	Ronny
14
15
Sheet1

 

[JEC]

Is VBA ok?

Sub jec()
 Dim c00, it
 With CreateObject("scripting.dictionary")
   For Each it In Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
      c00 = .Item(it.Value)
   Next
   Cells(2, 5).Resize(.Count) = Application.Transpose(.keys)
 End With
End Sub

 

[Sufiyan97]

Hello JEC
Thanks for the response
I am looking for the formula solution if possible.

 

[JGordon11]

Note that Microsoft offers Power Query for both Excel 2010 and Excel 2013 in both 32- and 64-bit platforms. ... From the Excel Ribbon, choose File→Options. Choose the Add-Ins option on the left, and then look for the Manage drop-down list at the bottom of the dialog box. Select COM Add-Ins and then click Go.

In Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Distinct(List.RemoveNulls(Source[List1] & Source[List2]))
in
    lst

Book2
	A	B	C	D	E
1	List1	List2		Table1
2	Dave	Dave		Dave
3	Dave	Dave		Dwayne
4	Dwayne	Dave		Joy
5	Dwayne	Dwayne		Amy
6	Dwayne	Joy		Ronny
7	Dwayne	Joy		Cary
8	Joy	Joy
9	Joy	Joy
10	Joy	Joy
11	Amy	Ronny
12	Amy	Cary
13	Ronny
14
Sheet1

Also in PQ the output can be sorted easily:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Sort(List.Distinct(List.RemoveNulls(Source[List1] & Source[List2])))
in
    lst

Book2
	A	B	C	D	E
1	List1	List2		Table1
2	Dave	Dave		Amy
3	Dave	Dave		Cary
4	Dwayne	Dave		Dave
5	Dwayne	Dwayne		Dwayne
6	Dwayne	Joy		Joy
7	Dwayne	Joy		Ronny
8	Joy	Joy
9	Joy	Joy
10	Joy	Joy
11	Amy	Ronny
12	Amy	Cary
13	Ronny
14
Sheet1

 

[JEC]

Ok how about (array formula)

=INDIRECT(TEXT(MIN(IF(($A$2:$B$13<>"")*(COUNTIF($E$1:E1,$A$2:$B$13)=0),ROW($2:$13)*100+COLUMN($A:$B),EXP(99))),"R00C00"),)

 

[AhoyNC]

Another option.

Book1
	A	B	C	D
1	List1	List2		Unique
2	Dave	Dave		Ronny
3	Dave	Dave		Amy
4	Dwayne	Dave		Joy
5	Dwayne	Dwayne		Dwayne
6	Dwayne	Joy		Dave
7	Dwayne	Joy		Cary
8	Joy	Joy
9	Joy	Joy
10	Joy	Joy
11	Amy	Ronny
12	Amy	Cary
13	Ronny
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=IFNA(IFERROR(LOOKUP(2,1/(COUNTIF($D$1:D1,$A$2:$A$13)=0),$A$2:$A$13),LOOKUP(2,1/(COUNTIF($D$1:D1,$B$2:$B$12)=0),$B$2:$B$12)),"")

 

[Sufiyan97]

Hello @AhoyNC

This works perfect

just one thing if we can do like I have changed range to 500 rows so we don't need to change formula every time, so I am getting 0 in D2.

can we modify it so it does not show 0?

Book1
	A	B	C	D	E
1	List1	List2		Unique
2	Dave	Dave		0
3	Dave	Dave		Ali
4	Dwayne	Dave		Ronny
5	Dwayne	Dwayne		Amy
6	Dwayne	Joy		Joy
7	Dwayne	Joy		Dwayne
8	Joy	Joy		Dave
9	Joy	Joy		Moin
10	Joy	Joy		Cary
11	Amy	Ronny
12	Amy	Cary
13	Ronny	Ali
14	Ali	Moin
15
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=IFNA(IFERROR(LOOKUP(2,1/(COUNTIF($D$1:D1,$A$2:$A$500)=0),$A$2:$A$500),LOOKUP(2,1/(COUNTIF($D$1:D1,$B$2:$B$500)=0),$B$2:$B$500)),"")

 

[AhoyNC]

Try:

Book1 (version 1).xlsb
	A	B	C	D
1	List1	List2		Unique
2	Dave	Dave		Ronny
3	Dave	Dave		Amy
4	Dwayne	Dave		Joy
5	Dwayne	Dwayne		Dwayne
6	Dwayne	Joy		Dave
7	Dwayne	Joy		Cary
8	Joy	Joy
9	Joy	Joy
10	Joy	Joy
11	Amy	Ronny
12	Amy	Cary
13	Ronny
14
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=IFNA(IFERROR(LOOKUP(2,1/(IF($A$2:$A$500<>"",COUNTIF($D$1:D1,$A$2:$A$500)=0)),$A$2:$A$500),LOOKUP(2,1/(IF($B$2:$B$500<>"",COUNTIF($D$1:D1,$B$2:$B$500)=0)),$B$2:$B$500)),"")

 

[Sufiyan97]

Thank you very much @AhoyNC
Working Perfect!

 

[AhoyNC]

You're welcome. Thanks for the feedback.