Summarize base data

[sharshra]

I have base data table having the data with varying results for ids. I have to summarize this in a summary table based on the logic mentioned below. Not sure how this can be done. Can the experts in this forum advise please?

Logic to summarize in summary table -
- Each id should have one occurrence.
- If there is any `no´, update only `no´ in the result column even if there are other results for an id. Refer 2, 3 in the tables below.
- If there are multiple results for same id other than `no´, concatenate all results & display against that id. Refer 4, 5 in the tables below.

Base table -

excel problems.xlsx
	C	D
2	id	result
3	1	yes
4	2	no
5	3	yes
6	3	no
7	3	na
8	4	na
9	4	yes
10	5	na
11	5	a
12	5	b
13	6
14	7
Sheet3

Summary table -

excel problems.xlsx
	F	G
2	id	result
3	1	yes
4	2	no
5	3	no
6	4	na, yes
7	5	na, a, b
8	6
9	7
Sheet3

 

[Fluff]

How about

Fluff.xlsm
	C	D	E	F	G
1
2	id	result		id	result
3	1	yes		1	yes
4	2	no		2	no
5	3	yes		3	no
6	3	no		4	na, yes
7	3	na		5	na, a, b
8	4	na		6
9	4	yes		7
10	5	na
11	5	a
12	5	b
13	6
14	7
Sheet6

Cell Formulas
Range	Formula
F3:F9	F3	=UNIQUE(FILTER(C3:C100,C3:C100<>""))
G3:G9	G3	=IF(COUNTIFS(C:C,F3,D:D,"no"),"no",TEXTJOIN(", ",,FILTER($D$3:$D$100,$C$3:$C$100=F3)))
Dynamic array formulas.

 

[RobP]

@fluffs code is the brains that make it work - as I tried and failed
But I took it, and tweaked it a little to give a single formula instead, in case you didn't want to copy down the rows - but you will have to fix your range sizes accordingly.

=HSTACK(UNIQUE(C3:C14),SCAN(0,UNIQUE(C3:C14),LAMBDA(a,v,IF(COUNTIFS(C3:C14,v,D3:D14,"no"),"no",TEXTJOIN(", ",,FILTER($D$3:$D$14,$C$3:$C$14=v))))))

If the solution works for you .. like @fluffs not mine.

 

[Fluff]

Another option for an all-in-one

=LET(u,UNIQUE(FILTER(C3:C100,C3:C100<>"")),HSTACK(u,MAP(u,LAMBDA(m,IF(COUNTIFS(C:C,m,D:D,"no"),"no",TEXTJOIN(", ",,FILTER(D3:D100,C3:C100=m)))))))

 

[sharshra]

Fantastic Thanks a lot @Fluff. Really appreciate your help. I´m amazed how quickly you find a solution
Thanks to @RobP too

 

[Fluff]

Glad we could help & thanks for the feedback.