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 -
Summary table -
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 | ||||
