I would like to concatenate multiple related values into one cell

[yashwanthkumar]

Hi there,

I am trying to concatenate the related assignment numbers of students into one cell. The data in first 2 columns is what I have and I am trying to achieve the result as per third column. can some please assist me on how to do this?
there 1000 row items, each student will have different assignment numbers and can appear upto 10 times, what I need is all the assignment numbers belonging to a particular student to appear in a single cell with comma separators, is this doable?

Name	Assignment Numbers	What I need is
ABC	2	2,3,4
ABC	3
ABC	4
BBC	1	1,7
BBC	7

 

[Fluff]

If you have the TextJoin function, try

Book1
	A	B	C
1	Name	Assignment Numbers	What I need is
2	ABC	2	2,3,4
3	ABC	3
4	ABC	4
5	BBC	1	1,7
6	BBC	7
Database

Cell Formulas
Range	Formula
C2:C6	C2	{=IF(COUNTIF(A$2:A2,A2)=1,TEXTJOIN(",",1,IF($A$2:$A$6=A2,$B$2:$B$6,"")),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[KOKOSEK]

I don't know what version of Excel you are using. I've used TEXTJOIN function:

	A​	B​	C​
1​	Name	Assignment Numbers	What I need is
2​	ABC	2​	2, 3, 4
3​	ABC	3​
4​	ABC	4​
5​	BBC	1​	1, 7
6​	BBC	7​

	A​	B​	C​
1​	Name	Assignment Numbers	What I need is
2​	ABC	2​	=IF(A2<>A1,TEXTJOIN(", ",1,IF($A$2:$A$20=A2,$B$2:$B$20,"")),"")

Formula in C2 is array type so you have to accept it with Ctrl+Shift+Enter (not just Enter as usual). You should spot that curly brackets {} appear around formula in formula bar.
Then drag it down as long as needed.

 

[yashwanthkumar]

Thank you very much, that worked exactly the way I wanted....

 

[Fluff]

Not sure which of us you're talking to, but glad we could help & thanks for the feedback

 

[KOKOSEK]

Does not matter Fluff.
The most important part is the same in both solutions and both based on textjoin.
Happy to help.

 

[Fluff]

They will only give the same result if the data is sorted by Name, which it is in the op, otherwise the results are different.

Book1
	A	B	C	D
1	Name	Assignment Numbers	Fluff	Kokosek
2	BBC	1	1,7	1, 7
3	ABC	2	2,4,8	2, 4, 8
4	ABC	4
5	BBC	7		1, 7
6	ABC	8		2, 4, 8
Database

But the OP is happy & that's all that counts.

 

[KOKOSEK]

Sure, that's why I said 'the most important part'. Even in your table above all results are correct. Mine just showing CORRECT result for all appearances of particular Name, Yours only for first appearance.