Combine Cells when same Value in another column

[Dani_LobP]

Hi,

I'm struggling to find a way to make some textjoin alike formula with IF condition and also "unique" too in order to combine results that share same value.

I have something as per below example:

ID	Type
Name1	Type1
Name1	Type2
Name2	Type2
Name3	Type1
Name4	Type1
Name4	Type1
Name4	Type1

And the result I would like to obtain, is to display for each Name, what combinations they have..

Name1 should display Type1-Type2
Name2 should display Type2
Name3 should display Type1
Name4 should display Type1-Type1

=TEXTJOIN("-",TRUE,SORT(UNIQUE(IF([ID]=[@[ID]],[Type],""))))

Ofc if using Unique, it wont allow repetition as Type1-Type1 for example, but unsure of how other option i can do so it allows at least 1 duplication...

Hope it makes sense the explanation and the goal.

thanks in advance!

 

[etaf]

how about
=TEXTJOIN(",",,SORT(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=D2))))

Book6
	A	B	C	D	E
1	ID	Type		ID
2	Name1	Type1		Name1	Type1,Type2
3	Name1	Type2		Name2	Type2
4	Name2	Type2		Name3	Type1
5	Name3	Type1		Name4	Type1
6	Name4	Type1		0
7	Name4	Type1
8	Name4	Type1
Sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=UNIQUE(A:A)
E2:E5	E2	=TEXTJOIN(",",,SORT(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=D2))))
Dynamic array formulas.

 

[Dani_LobP]

how about
=TEXTJOIN(",",,SORT(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=D2))))

Book6
	A	B	C	D	E
1	ID	Type		ID
2	Name1	Type1		Name1	Type1,Type2
3	Name1	Type2		Name2	Type2
4	Name2	Type2		Name3	Type1
5	Name3	Type1		Name4	Type1
6	Name4	Type1		0
7	Name4	Type1
8	Name4	Type1
Sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=UNIQUE(A:A)
E2:E5	E2	=TEXTJOIN(",",,SORT(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=D2))))
Dynamic array formulas.

Thanks, this works partially, since i believe it makes same result i obtain, those cases with Type1-Type1, will appear just as type1... and somehow i need to display Type1-Type1 ... but not type1-type1-type1 ... if that makes sense
in some way i want "uniques" up to 2 times duplicated... not sure if its actually possible.

 

[hagia_sofia]

Thanks, this works partially, since i believe it makes same result i obtain, those cases with Type1-Type1, will appear just as type1... and somehow i need to display Type1-Type1 ... but not type1-type1-type1 ... if that makes sense
in some way i want "uniques" up to 2 times duplicated... not sure if its actually possible.

Hello, I have a question concerning the data - is it possible that for some names there are several types, e.g. type 1 - type 1 - type 1 - type - 2 - type 2? I.e. the expected outcome would be type 1 - type 1 - type 2 - type 2?

 

[etaf]

not sure how to do that , i will have a look into - hopefully another member may have a solution

 

[Dani_LobP]

Hello, I have a question concerning the data - is it possible that for some names there are several types, e.g. type 1 - type 1 - type 1 - type - 2 - type 2? I.e. the expected outcome would be type 1 - type 1 - type 2 - type 2?

Yes, it can happen.
the Type is actually connected to the name of a person, so type1-Type1 means that 2 different persons checked Name1. So type1-Type1-type2-type2 would mean that 4 different persons checked the Name, and they are split in 2 and 2 types ... if that makes sense.

 

[etaf]

not sure i follow - can you give the example
type1 appears 3 times for name 4
so you want that twice

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	ID	Type
2	Name1	Type1		Name1	Type1-Type2
3	Name1	Type2		Name2	Type2
4	Name2	Type2		Name3	Type1
5	Name3	Type1		Name4	Type1-Type1-Type2
6	Name4	Type1
7	Name4	Type2
8	Name4	Type1
Data

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(TOCOL(A2:A100,1))
E2:E5	E2	=LET(f,FILTER($B$2:$B$100,$A$2:$A$100=D2),r,ROWS(f),TEXTJOIN("-",,SORT(FILTER(f,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(f=TOROW(f)),SEQUENCE(r,,,0))<=2))))
Dynamic array formulas.

 

[hagia_sofia]

Here is another option (but Fluff's solution is definitely more elegant):

=LET(
array,SORT(FILTER($B$2:$B$8,$A$2:$A$8=D2)),
count,CHOOSECOLS(HSTACK(UNIQUE(array),MAP(UNIQUE(array),LAMBDA(x,SUM(--(array=x))))),2),
sequence,DROP(REDUCE("",count,LAMBDA(a,b,VSTACK(a,SEQUENCE(b)))),1),
TEXTJOIN("-",,FILTER(array,sequence<=2)))

 

[Dani_LobP]

How about

Fluff.xlsm
	A	B	C	D	E
1	ID	Type
2	Name1	Type1		Name1	Type1-Type2
3	Name1	Type2		Name2	Type2
4	Name2	Type2		Name3	Type1
5	Name3	Type1		Name4	Type1-Type1-Type2
6	Name4	Type1
7	Name4	Type2
8	Name4	Type1
Data

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(TOCOL(A2:A100,1))
E2:E5	E2	=LET(f,FILTER($B$2:$B$100,$A$2:$A$100=D2),r,ROWS(f),TEXTJOIN("-",,SORT(FILTER(f,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(f=TOROW(f)),SEQUENCE(r,,,0))<=2))))
Dynamic array formulas.

thanks, that's somehow what I was trying to find. I had to edit a bit the data after spotting some weird results but now works perfect. thanks!