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.
Solved! Go to Solution.
Hi @tsbryan
If there are truly no overlaps, just union the data and then take the max value (non null) for each name:
Luke,
Many thanks, worked perfectly.