Hi all,
I'm trying to merge columns with common names and keep the value associated with it. Ideally, I'd like to group all columns that have the name "RAT" and keep the positive/negative.
So, if column 1 is empty, then column 2. if column 2 is empty, then column 3. if all are empty, then "not tested".
Input:
| ID | RAT_1 | RAT_2 | RAT_3 |
| 1 | Positive | | |
| 2 | | | Negative |
| 3 | Indeterminate | | Positive |
| 4 | | | |
| 5 | | Negative | |
| 6 | | | Positive |
| 7 | | | |
| 9 | Positive | | |
| 10 | | | |
Output:
| ID | RAT_1 | RAT_2 | RAT_3 | Combined |
| 1 | Positive | | | Positive |
| 2 | | | Negative | Negative |
| 3 | Indeterminate | | Positive | Positive |
| 4 | | | | Not Tested |
| 5 | | Negative | | Negative |
| 6 | | | Positive | Positive |
| 7 | | | | Not Tested |
| 9 | Positive | | | Positive |
| 10 | | | | Not Tested |
Thanks!
