How to Merge and concatenate entries based on name

[achusp]

Hi,

I'm a beginner in excel. I'm struggling to achieve the result as given below. I have tried Indexing but it stops at the first instance.
I'm using Excel for MS 365 v2209

DATA TABLE​

File ID	File Name	Responsible	File Comments	Blocking Issue
100001	FName 100001	Person 1	Comment 1	Blocking Issue 1
100002	FName 100002	Person 2	Comment 2	None
100003	FName 100003	Person 1	Comment 3	Blocking Issue 2
100004	FName 100004	Person 2	Comment 4	Blocking Issue 3
100005	FName 100005	Person 1	Comment 5	None

RESULTING TABLE​

Sl. No.	Name	FILE ID	FILE NAME	Comments	Blocks
1	Person 1	100001	FName 100001	Comment 1	Blocking Issue 1
2	Person 1	100003	FName 100003	Comment 3	Blocking Issue 2
3	Person 1	100005	FName 100005	Comment 5	None
4	Person 2	100002	FName 100002	Comment 2	None
5	Person 2	100004	FName 100004	Comment 4	Blocking Issue 3

OR

Sl. No.	Name	FILE ID	FILE NAME	Comments	Blocks
1	Person 1	100001 100003 100005	FName 100001 FName 100003 Fname 100005	Comment 1 Comment 3 Comment 5	Blocking Issue 1 Blocking Issue 2 None
2	Person 2	100002 100004	FName 100002 FName 100004	Comment 2 Comment 4	None Blocking Issue 3

Is this possible using Excel commands only ? I have seen excel commands but I have no experience in VBA/Macros if they are needed for these.

Could someone please help me to figure out how I can achieve this?

 

[alansidman]

Why not just sort by the "Responsible" column? Data Sort.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	File ID	File Name	Responsible	File Comments	Blocking Issue
2	100001	FName 100001	Person 1	Comment 1	Blocking Issue 1
3	100002	FName 100002	Person 2	Comment 2	None
4	100003	FName 100003	Person 1	Comment 3	Blocking Issue 2
5	100004	FName 100004	Person 2	Comment 4	Blocking Issue 3
6	100005	FName 100005	Person 1	Comment 5	None
7
8
9
10	Sl. No.	Name	FILE ID	FILE NAME	Comments	Blocks
11	1	Person 1	100001	FName 100001	Comment 1	Blocking Issue 1
12	2	Person 1	100003	FName 100003	Comment 3	Blocking Issue 2
13	3	Person 1	100005	FName 100005	Comment 5	None
14	4	Person 2	100002	FName 100002	Comment 2	None
15	5	Person 2	100004	FName 100004	Comment 4	Blocking Issue 3
16
Data

Cell Formulas
Range	Formula
A11:F15	A11	=HSTACK(SEQUENCE(ROWS(A2:A6)),SORT(CHOOSECOLS(A2:E6,3,1,2,4,5)))
Dynamic array formulas.