Summarize base data

sharshra

Active Member
Joined
Mar 20, 2013
Messages
404
Office Version
  1. 365
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
CD
2idresult
31yes
42no
53yes
63no
73na
84na
94yes
105na
115a
125b
136
147
Sheet3


Summary table -
excel problems.xlsx
FG
2idresult
31yes
42no
53no
64na, yes
75na, a, b
86
97
Sheet3
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
How about
Fluff.xlsm
CDEFG
1
2idresultidresult
31yes1yes
42no2no
53yes3no
63no4na, yes
73na5na, a, b
84na6 
94yes7 
105na
115a
125b
136
147
Sheet6
Cell Formulas
RangeFormula
F3:F9F3=UNIQUE(FILTER(C3:C100,C3:C100<>""))
G3:G9G3=IF(COUNTIFS(C:C,F3,D:D,"no"),"no",TEXTJOIN(", ",,FILTER($D$3:$D$100,$C$3:$C$100=F3)))
Dynamic array formulas.
 
Upvote 0
@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.

Excel Formula:
=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.
 
Upvote 1
Another option for an all-in-one
Excel Formula:
=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)))))))
 
Upvote 1
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top