Concatenate Unique Values but remove the "/" where the valuue is unique

[VBA learner ITG]

Hi all,

I need your guidance if possible on how to amend the below array formula?

=TEXTJOIN("/", TRUE, IF(MATCH(AN4:AN23, AN4:AN23, 0)=MATCH(ROW(AN4:AN23), ROW(AN4:AN23)), AN4:AN23, ""))

Currently it adds the "/" as a pefix to the end of all values but i need this removing so it shows like the below format.

Example 1 unique value only:

80g

Example 2

80g/90g

 

[AhoyNC]

If your version of Excel 365 has the UNIQUE function then maybe something like this:

Book1
	A	B
1	Example 1
2	80g
3	45g	80g/45g/70g
4	70g
5	45g
6	80g
7
8	Example 2
9	80g	80g
10	80g
11	80g
12	80g
Sheet1

Cell Formulas
Range	Formula
B3	B3	=TEXTJOIN("/",1,UNIQUE(A2:A6))
B9	B9	=TEXTJOIN("/",1,UNIQUE(A9:A12))

 

[Peter_SSs]

Currently it adds the "/" as a pefix to the end of all values but i need this removing so it shows like the below format.

Whilst AhoyNC has given you a simple formula if you have the UNIQUE function, I don't understand what you are saying about your current formula??

Example - 1 unique value only. There is no "/" here

20 07 28.xlsm
	AN	AO
4	aa	aa
5	aa
6	aa
7	aa
8	aa
9	aa
10	aa
11	aa
12	aa
13	aa
14	aa
15	aa
16	aa
17	aa
18	aa
19	aa
20	aa
21	aa
22	aa
23	aa
TEXTJOIN

Cell Formulas
Range	Formula
AO4	AO4	=TEXTJOIN("/", TRUE, IF(MATCH(AN4:AN23, AN4:AN23, 0)=MATCH(ROW(AN4:AN23), ROW(AN4:AN23)), AN4:AN23, ""))

Example - More than 1 unique value. Output appears to be as you are asking for.

20 07 28.xlsm
	AN	AO
4	aa	aa/bb/cc/x
5	bb
6	cc
7	bb
8	cc
9	x
10	x
11	x
12	x
13	x
14	x
15	x
16	x
17	x
18	x
19	x
20	x
21	x
22	x
23	x
TEXTJOIN

Cell Formulas
Range	Formula
AO4	AO4	=TEXTJOIN("/", TRUE, IF(MATCH(AN4:AN23, AN4:AN23, 0)=MATCH(ROW(AN4:AN23), ROW(AN4:AN23)), AN4:AN23, ""))

=TEXTJOIN("/", TRUE, IF(MATCH(AN4:AN23, AN4:AN23, 0)=MATCH(ROW(AN4:AN23), ROW(AN4:AN23)), AN4:AN23, ""))
BTW, you can write your formula a bit more simply like this
=TEXTJOIN("/", TRUE, IF(MATCH(AN4:AN23, AN4:AN23, 0)=ROW(AN4:AN23)-ROW(AN4)+1, AN4:AN23,""))

 

[VBA learner ITG]

Thank you for replying & taking your time out of your busy day.

After posting this thread I found out the root cause of the issue as the range I was applying the formula above had an erroneous space which caused the formula to apply the "/" prefix.

 

[Peter_SSs]

had an erroneous space

That was going to be my next suggestion. Glad you figured it out.

.. although if you have the UNIQUE function AhoyNC has given a much simple formula.