Group Columns with similar names

[gbufalino]

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!

[Raj_Singh1]

Hi @gbufalino 

please see attached. let me know if this is the solution you are looking for? happy to make changes 🙂

[Qiu]

@gbufalino 
Can elaborate your logic here.

---

So, if column 1 is empty, then column 2. if column 2 is empty, then column 3. if all are empty, then "not tested".

---

It seems that ID 3 does not comply with above logic?

[gbufalino]

I ran this and my "combined" column only returned with "NOT TESTED". 

[gbufalino]

I'm not sure how to explain this, so I was hoping the input/output would help visually... there are only 4 possible test results:

Positive

Negative

Indeterminate

Not Tested

If a row has indeterminate, but also has positive/negative, I need the pos/neg value. 

If a row only has a single value (regardless of that value), I need that value.

If a row has no value, then it was "not tested".

[Qiu · Accepted solution]

@gbufalino 

Thank you for the clarification

I have to assume that the Positive will prevail if positive/negative both present.

[gbufalino]

This worked. Thanks so much!

[Qiu]

@gbufalino 

Thank you for the accept mark.