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 |