Join Only Unique Values separate By Vertical Bar

[motilulla]

Hello,

I have B:F 5 columns in which there are unique 5 values or may max 2 or 3 duplicate values I want to Join Only Unique Values separate By Vertical Bar in the Column H as shown below

For a more detail the image is attached. Pease suggest formula or VBA

*	A	B	C	D	E	F	G	H	I
1
2
3
4								Join Only Unique Values
5		n1	n2	n3	n4	n5		Separate By Vertical Bar
6		8	8	18	21	21		8 | 18 | 21
7		2	20	20	25	25		2 | 20 | 25
8		2	10	10	13	13		2 | 10 | 13
9		3	5	11	23	25		3 | 5 | 11 | 23 | 25
10		1	2	11	16	20		1 | 2 | 11 | 16 | 20
11		8	15	19	20	25		8 | 15 | 19 | 20 | 25
12		3	6	18	21	22		3 | 6 | 18 | 21 | 22
13		1	4	4	5	24		1 | 4 | 5 | 24
14		2	12	12	14	17		2 | 12 | 14 | 17
15		1	12	14	20	22		1 | 12 | 14 | 20 | 22
16		2	11	15	17	18		2 | 11 | 15 | 17 | 18
17		1	3	6	10	22		1 | 3 | 6 | 10 | 22
18		5	5	10	19	25		5 | 10 | 19 | 25
19		3	5	5	14	18		3 | 5 | 14 | 18
20		18	18	21	23	25		18 | 21 | 23 | 25
21		13	13	15	25	25		13 | 15 | 25
22		7	12	12	21	21		7 | 12 | 21
23		1	6	11	11	15		1 | 6 | 11 | 15
24		5	6	10	10	12		5 | 6 | 10 | 12
25		13	18	19	19	21		13 | 18 | 19 | 21
26		2	6	12	14	18		2 | 6 | 12 | 14 | 18
27		1	2	5	10	12		1 | 2 | 5 | 10 | 12
28		2	2	7	22	25		2 | 7 | 22 | 25
29		3	6	24	24	25		3 | 6 | 24 | 25
30		4	5	14	16	17		4 | 5 | 14 | 16 | 17
31		3	8	12	18	22		3 | 8 | 12 | 18 | 22
32		10	14	21	22	25		2 | 12 | 14 | 14 | 14
33		2	14	15	22	22		2 | 14 | 15 | 22
34		13	13	21	22	24		13 | 21 | 22 | 24
35		6	9	16	16	20		6 | 9 | 16 | 20
36
37

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[kevin9999]

Try the following in cell H6 & copy down.

=TEXTJOIN("|",TRUE,UNIQUE(B6:F6,TRUE))

 

[motilulla]

Try the following in cell H6 & copy down.

=TEXTJOIN("|",TRUE,UNIQUE(B6:F6,TRUE))

kevin9999, it gives me result #NAME? I guess may be the formula is not computable with my Xl2000 version

Thank you for your help

God Luck

Kind Regards
Moti

 

[kevin9999]

Sorry, I didn't notice your version of Excel is 2003 or older. That's why it didn't work.

 

[motilulla]

Sorry, I didn't notice your version of Excel is 2003 or older. That's why it didn't work.

kevin9999, thank you and no problem at all there would be definitely some larger formula which will work with my version let wait if some one can come across.

Kind Regards
Moti

 

[bebo021999]

All-F11 to open VBA windows. Insert new modul then past below code. Hit F5 to run.

Option Explicit
Sub test()
Dim Lr, i, j, k As Long
Dim dic As Object
Dim rng, key As Variant
Dim st As String
Set dic = CreateObject("Scripting.dictionary")
Lr = Cells(Rows.Count, "B").End(xlUp).Row ' dynamic range
For i = 6 To Lr
    k = 0
    st = ""
    For j = 1 To 5 ' adjust 5 to last column, if needed
    rng = Range("B" & i).Offset(, j - 1).Value
        If Not dic.exists(rng) Then ' create list of unique number
        k = k + 1
        dic.Add rng, k
        End If
    Next
    For Each key In dic.keys ' loop through each unique value then concatenate
    st = st & key & "|"
    Next
    Range("H" & i).Value = Left(st, Len(st) - 1) 'write down to cell
    dic.RemoveAll
Next
End Sub

 

[motilulla]

All-F11 to open VBA windows. Insert new modul then past below code. Hit F5 to run.

Option Explicit
Sub test()
Dim Lr, i, j, k As Long
Dim dic As Object
Dim rng, key As Variant
Dim st As String
Set dic = CreateObject("Scripting.dictionary")
Lr = Cells(Rows.Count, "B").End(xlUp).Row ' dynamic range
For i = 6 To Lr
    k = 0
    st = ""
    For j = 1 To 5 ' adjust 5 to last column, if needed
    rng = Range("B" & i).Offset(, j - 1).Value
        If Not dic.exists(rng) Then ' create list of unique number
        k = k + 1
        dic.Add rng, k
        End If
    Next
    For Each key In dic.keys ' loop through each unique value then concatenate
    st = st & key & "|"
    Next
    Range("H" & i).Value = Left(st, Len(st) - 1) 'write down to cell
    dic.RemoveAll
Next
End Sub

Wow! bebo021999, special VBA coding worked like magic! And you written an easy way so any one can change the address very easily

I appreciate you help I like the macro

Good Luck

Kind Regards
Moti

 

[bebo021999]

Cheer! Nice to hear it works!

 

[Peter_SSs]

Pease suggest formula or VBA

Since you also asked for a formula suggestion and you only have 5 columns it would be feasible to use standard worksheet functions.

21 12 09.xlsm
	B	C	D	E	F	G	H
5	n1	n2	n3	n4	n5
6	8	8	18	21	21		8 | 18 | 21
7	2	20	20	25	25		2 | 20 | 25
8	2	10	10	13	13		2 | 10 | 13
9	3	5	11	23	25		3 | 5 | 11 | 23 | 25
10	1	2	11	16	20		1 | 2 | 11 | 16 | 20
11	8	15	19	20	25		8 | 15 | 19 | 20 | 25
12	3	6	18	21	22		3 | 6 | 18 | 21 | 22
13	1	4	4	5	24		1 | 4 | 5 | 24
14	2	12	12	14	17		2 | 12 | 14 | 17
15	1	12	14	20	22		1 | 12 | 14 | 20 | 22
16	2	11	15	17	18		2 | 11 | 15 | 17 | 18
17	1	3	6	10	22		1 | 3 | 6 | 10 | 22
18	5	5	10	19	25		5 | 10 | 19 | 25
19	3	5	5	14	18		3 | 5 | 14 | 18
20	18	18	21	23	25		18 | 21 | 23 | 25
21	13	13	15	25	25		13 | 15 | 25
22	7	12	12	21	21		7 | 12 | 21
23	1	6	11	11	15		1 | 6 | 11 | 15
24	5	6	10	10	12		5 | 6 | 10 | 12
25	13	18	19	19	21		13 | 18 | 19 | 21
26	2	6	12	14	18		2 | 6 | 12 | 14 | 18
27	1	2	5	10	12		1 | 2 | 5 | 10 | 12
28	2	2	7	22	25		2 | 7 | 22 | 25
29	3	6	24	24	25		3 | 6 | 24 | 25
30	4	5	14	16	17		4 | 5 | 14 | 16 | 17
31	3	8	12	18	22		3 | 8 | 12 | 18 | 22
32	10	14	21	22	25		10 | 14 | 21 | 22 | 25
33	2	14	15	22	22		2 | 14 | 15 | 22
34	13	13	21	22	24		13 | 21 | 22 | 24
35	6	9	16	16	20		6 | 9 | 16 | 20
Join

Cell Formulas
Range	Formula
H6:H35	H6	=B6&IF(C6<>B6," | "&C6,"")&IF(MATCH(D6,B6:D6,0)=3," | "&D6,"")&IF(MATCH(E6,B6:E6,0)=4," | "&E6,"")&IF(MATCH(F6,B6:F6,0)=5," | "&F6,"")

 

[Peter_SSs]

Actually, if the numbers are ascending in each row like your samples, then this should do it.

21 12 09.xlsm
	B	C	D	E	F	G	H
5	n1	n2	n3	n4	n5
6	8	8	18	21	21		8 | 18 | 21
7	2	20	20	25	25		2 | 20 | 25
8	2	10	10	13	13		2 | 10 | 13
9	3	5	11	23	25		3 | 5 | 11 | 23 | 25
10	1	2	11	16	20		1 | 2 | 11 | 16 | 20
11	8	15	19	20	25		8 | 15 | 19 | 20 | 25
12	3	6	18	21	22		3 | 6 | 18 | 21 | 22
13	1	4	4	5	24		1 | 4 | 5 | 24
14	2	12	12	14	17		2 | 12 | 14 | 17
15	1	12	14	20	22		1 | 12 | 14 | 20 | 22
16	2	11	15	17	18		2 | 11 | 15 | 17 | 18
17	1	3	6	10	22		1 | 3 | 6 | 10 | 22
18	5	5	10	19	25		5 | 10 | 19 | 25
19	3	5	5	14	18		3 | 5 | 14 | 18
20	18	18	21	23	25		18 | 21 | 23 | 25
21	13	13	15	25	25		13 | 15 | 25
22	7	12	12	21	21		7 | 12 | 21
23	1	6	11	11	15		1 | 6 | 11 | 15
24	5	6	10	10	12		5 | 6 | 10 | 12
25	13	18	19	19	21		13 | 18 | 19 | 21
26	2	6	12	14	18		2 | 6 | 12 | 14 | 18
27	1	2	5	10	12		1 | 2 | 5 | 10 | 12
28	2	2	7	22	25		2 | 7 | 22 | 25
29	3	6	24	24	25		3 | 6 | 24 | 25
30	4	5	14	16	17		4 | 5 | 14 | 16 | 17
31	3	8	12	18	22		3 | 8 | 12 | 18 | 22
32	10	14	21	22	25		10 | 14 | 21 | 22 | 25
33	2	14	15	22	22		2 | 14 | 15 | 22
34	13	13	21	22	24		13 | 21 | 22 | 24
35	6	9	16	16	20		6 | 9 | 16 | 20
Join (2)

Cell Formulas
Range	Formula
H6:H35	H6	=B6&IF(C6<>B6," | "&C6,"")&IF(D6<>C6," | "&D6,"")&IF(E6<>D6," | "&E6,"")&IF(F6<>E6," | "&F6,"")