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

 

[Peter_SSs]

Actually, if the numbers are ascending in each row like your samples, ..

.. then this vba should also do it.

Sub JoinThem()
  Dim a As Variant
  Dim i As Long, j As Long

  a = Range("B6", Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
  For i = 1 To UBound(a)
    For j = 2 To 5
      If a(i, j) <> a(i, j - 1) Then a(i, 1) = a(i, 1) & " | " & a(i, j)
    Next j
  Next i
  Range("H6").Resize(UBound(a)).Value = a
End Sub

 

[Tom.Jones]

@Peter_SSs,

Your formula fail for:

n1	n2	n3	n4	n5
8	18	18	21	21
2	20	20	25	25
2	13	10	13	13

 

[Peter_SSs]

@Peter_SSs,

Your formula fail for:

n1	n2	n3	n4	n5
8	18	18	21	21
2	20	20	25	25
2	13	10	13	13

Which formula fails?

Post #9 formula works as far as I can see?

Post #10 formula was preceded by the following so would not be relevant to your sample data.

if the numbers are ascending in each row like your samples,

 

[Tom.Jones]

Peter_SSs said:
if the numbers are ascending in each row like your samples,

That was and refresh was manually.

 

[Peter_SSs]

That was and refresh was manually.

I don't understand that comment, but since I see you have edited your previous post perhaps it doesn't matter any more.

 

[motilulla]

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, I love the formula it worked very well I used this one because it is universal does not care for the sequence of numbers are in ascending or descending great formula

.. then this vba should also do it.

Sub JoinThem()
  Dim a As Variant
  Dim i As Long, j As Long

  a = Range("B6", Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
  For i = 1 To UBound(a)
    For j = 2 To 5
      If a(i, j) <> a(i, j - 1) Then a(i, 1) = a(i, 1) & " | " & a(i, j)
    Next j
  Next i
  Range("H6").Resize(UBound(a)).Value = a
End Sub

Peter, Also macro worked fine and noticed that has spacing in between separate By Vertical Bar great job

I appreciate you help I like the both options

Good Luck

Kind Regards
Moti

 

[Peter_SSs]

You're welcome.

noticed that has spacing in between separate By Vertical Bar

My two formulas and my vba all did that because that is what you have in your column H values in post #1.

For completeness, if the numbers can be in any order in the row then my vba suggestion would change to ..

Sub JoinThem_v2()
  Dim a As Variant
  Dim i As Long, j As Long

  a = Range("B6", Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
  For i = 1 To UBound(a)
    For j = 2 To 5
      If InStr(1, " " & a(i, 1) & " ", " " & a(i, j) & " ") = 0 Then a(i, 1) = a(i, 1) & " | " & a(i, j)
    Next j
  Next i
  Range("H6").Resize(UBound(a)).Value = a
End Sub

 

[Tom.Jones]

I don't understand that comment, but since I see you have edited your previous post perhaps it doesn't matter any more.

Tom.Jones said:
That was and refresh was manually.

Somehow the recalculation was not automatic.
(file-> options-> formulas-> workbook calculation-> manual)

 

[motilulla]

You're welcome.


My two formulas and my vba all did that because that is what you have in your column H values in post #1.

For completeness, if the numbers can be in any order in the row then my vba suggestion would change to ..

Sub JoinThem_v2()
  Dim a As Variant
  Dim i As Long, j As Long

  a = Range("B6", Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
  For i = 1 To UBound(a)
    For j = 2 To 5
      If InStr(1, " " & a(i, 1) & " ", " " & a(i, j) & " ") = 0 Then a(i, 1) = a(i, 1) & " | " & a(i, j)
    Next j
  Next i
  Range("H6").Resize(UBound(a)).Value = a
End Sub

Peter, thank you so much for creating macro, this worked well with the numbers, could be in any order in the row.

Have a great weekend ?

Kind Regards
Moti