Joining/Merging Columns with the Same Name

[tsbryan]

Hello all. I am struggling with an awkward data set and need help merging responses into a single set. The data is for responses on a quiz, where "Y", "N", or [null] are possible responses. The issue is, that the "Y" responses and "N" responses are separated into two data sets and I need them to be merged into a single set... not as a Left Join/Right Join, if that makes sense.

Example:

Data Set 1:

	Q1	Q2	Q3
Bill	Yes	Yes	[null]
Charlie	[null]	Yes	[null]
Jane	[null]	[null]	Yes

Data Set 2:

	Q1	Q2	Q3
Bill	[null]	[null]	[null]
Charlie	No	[null]	No
Jane	No	[null]	[null]

Desired Output:

	Q1	Q2	Q3
Bill	Yes	Yes	[null]
Charlie	No	Yes	No
Jane	No	[null]	Yes

The question becomes... is there a way to join the data without creating Q1 and Right_Q1, Q2 and Right_Q2, that combines the data in the desired way? There are no instances of Yes/No overlapping for an individual/question.

I've attached a spreadsheet with two tabs (Yes/No) as very small example.

Many thanks.

[Luke_C · Accepted solution]

Hi @tsbryan 

If there are truly no overlaps, just union the data and then take the max value (non null) for each name:

[tsbryan]

Luke,

Many thanks, worked perfectly.