Extracting unique values from a list in multiple columns into a single column (using formula)

[shakehands]

hi guys,

first of all, many thanks for your help.
I've been searching for the answer to my problem, but I can't seem to find one anywhere, especially inside my head. So I thought, maybe the excel masters here can give me some solution !

So here is the trouble :
I have 5 columns of data, which is sometimes unique, and sometimes has duplicate value.
Here is the example :

On Column A (Listed from row 1 (A1) to row 5 (A5) > I have a,b,c,d,f
On Column B (Listed from row 1 (B1) to row 5 (B5) > I have c,d,a,f,g
On Column C (Listed from row 1 (C1) to row 5 (C5) > I have a,c,b,f,g
On Column D (Listed from row 1 (D1) to row 5 (D5) > I have d,g,b,c,a
On Column E (Listed from row 1 (E1) to row 5 (E5) > I have a,c,d,e,f

This is the illustration :

---A---B--C--D---E
1| A | C | A | D | A
2| B | D | C | G | C
3| C | A | B | B | D
4| D | F | F | C | E
5| F | G | G | A | F

----------------
All I want is to list the result on column F, and here is how the result should be :

---F
1| A
2| B
3| C
4| D
5| E
6| F
7| G

Note : It doesn't have to be alphabetically ordered.
As long as it lists each of all the items in the previous 5 columns, that is totally awesome !

Thanks a lot guys!

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG25Apr17
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:E5")
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Trim(Dn.Value)) [COLOR="Navy"]Then[/COLOR]
        .Item(Trim(Dn.Value)) = Empty
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("F1").Resize(.Count) = Application.Transpose(.Keys)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[shakehands]

Hi MickG,thanks for your answer, but I'm looking for a formula. I have searched it all over the internet and finally found an answer.Here is the link, maybe it can help others who are looking for it.Extract a unique distinct list from three columns in excel | Get Digital Help - Microsoft Excel resource

 

[Aladin Akyurek]

Hi MickG,thanks for your answer, but I'm looking for a formula. I have searched it all over the internet and finally found an answer.Here is the link, maybe it can help others who are looking for it.Extract a unique distinct list from three columns in excel | Get Digital Help - Microsoft Excel resource

Also...

http://www.mrexcel.com/forum/excel-questions/721253-finding-unique-values-among-several-rows.html

 

[mukun]

Hey Guys, I have the same issue, but i have 8 columns from which i would like a single column of unique values.

This is the formula to build on:

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$20)+($A$2:$A$20=""), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$7)+($B$2:$B$7=""), 0))), INDEX($C$2:$C$12, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$12)+($C$2:$C$12=""), 0))), "")

 

[Aladin Akyurek]

Hey Guys, I have the same issue, but i have 8 columns from which i would like a single column of unique values.

This is the formula to build on:

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$20)+($A$2:$A$20=""), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$7)+($B$2:$B$7=""), 0))), INDEX($C$2:$C$12, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$12)+($C$2:$C$12=""), 0))), "")

Add the following code to your work, using Alt + F11:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Given the following data on Sheet1 (otherwise, adjust to suit the sheet name)...

Row\Col	A​	B​	C​	D​
1​				13​
2​	jad	jad	qad	RESULT
3​	kad	xad	jad	jad
4​	jad	lad	zad	kad
5​	nad	vad	gad	nad
6​	vad	vad	nad	vad
7​	wad	fad	uad	wad
8​	jad		xad	lad
9​	lad		TAD	UAD
10​	UAD		VAD	tad
11​	tad		nad	xad
12​	xad			fad
13​	tad			qad
14​	kad			zad
15​	tad			gad
16​	uad
17​	JAD
18​	VAD
19​	vad
20​	xad

Define List using Insert | Name | Define (or Formulas | Name Manager) as referring to:

=arrayunion(Sheet1!$A$2:$A$20,Sheet1!$B$2:$B$7,Sheet1!$C$2:$C$12)

Define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(List)))

In D1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(List=""),MATCH("~"&List,List&"",0)),Ivec),1))

In D3 control+shift+enter and copy down:

=IF(ROWS($D$3:D3)<=$D$1,INDEX(List,SMALL(IF(FREQUENCY(IF(1-(List=""),
    MATCH(List,List,0)),Ivec),Ivec),ROWS(D$3:D3))),"")

 

[mukun]

Add the following code to your work, using Alt + F11:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Given the following data on Sheet1 (otherwise, adjust to suit the sheet name)...

Row\Col	A​	B​	C​	D​
1​				13​
2​	jad	jad	qad	RESULT
3​	kad	xad	jad	jad
4​	jad	lad	zad	kad
5​	nad	vad	gad	nad
6​	vad	vad	nad	vad
7​	wad	fad	uad	wad
8​	jad		xad	lad
9​	lad		TAD	UAD
10​	UAD		VAD	tad
11​	tad		nad	xad
12​	xad			fad
13​	tad			qad
14​	kad			zad
15​	tad			gad
16​	uad
17​	JAD
18​	VAD
19​	vad
20​	xad

<tbody>
</tbody>

Define List using Insert | Name | Define (or Formulas | Name Manager) as referring to:

=arrayunion(Sheet1!$A$2:$A$20,Sheet1!$B$2:$B$7,Sheet1!$C$2:$C$12)

Define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(List)))

In D1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(List=""),MATCH("~"&List,List&"",0)),Ivec),1))

In D3 control+shift+enter and copy down:

=IF(ROWS($D$3:D3)<=$D$1,INDEX(List,SMALL(IF(FREQUENCY(IF(1-(List=""),
    MATCH(List,List,0)),Ivec),Ivec),ROWS(D$3:D3))),"")

Thank you for the response

I have been trying to make sense of this for the past 2 days.

So i defined list name and entered
=arrayunion(Sheet1!$A$2:$A$20,Sheet1!$B$2:$B$7,Sheet1!$C$2:$C$12)
where it says refers to in the dialog box.

Where do i define Ivec?

I have also included the code

Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

in Alt F11.

Your assistance is appreciated

 

[Aladin Akyurek]

Thank you for the response

I have been trying to make sense of this for the past 2 days.

So i defined list name and entered
=arrayunion(Sheet1!$A$2:$A$20,Sheet1!$B$2:$B$7,Sheet1!$C$2:$C$12)
where it says refers to in the dialog box.

Where do i define Ivec?

[...]

Also in the Name Manager... That's what "define" means.

Here is the workbook that implements the set up, the code for ARRAYUNION included: https://dl.dropboxusercontent.com/u...ns into a single column (using formula) .xlsm

 

[mukun]

Thanks Aladin

But it didnt work? could this be because i have blanks in my data?

 

[Aladin Akyurek]

Thanks Aladin

But it didnt work? could this be because i have blanks in my data?

What results did you get?