Hi! I hope someone can help me simplify my data.
Currently, I have data that looks like this (example):
ID | Gender | Age | Education | Special answer 1 | Special answer 2 | Special answer 3 |
1 | M | 20 | College | A | B | [Null] |
1 | M | 20 | College | [Null] | [Null] | C |
1 | M | 20 | College | [Null] | [Null] | [Null] |
1 | M | 20 | College | [Null] | [Null] | [Null] |
2 | F | 25 | High School | [Null] | B | [Null] |
2 | F | 25 | High School | D | [Null] | F |
3 | M | 30 | College | A | [Null] | [Null] |
3 | M | 30 | College | [Null] | G | [Null] |
I want it to look like this:
ID | Gender | Age | Education | Special answer 1 | Special answer 2 | Special answer 3 |
1 | M | 20 | College | A | B | C |
2 | F | 25 | High School | D | B | F |
3 | M | 30 | College | A | G | [Null] |
I don't how to combine or summarize the data so it combines this way. There won't always be an answer in the special answer field, so I need it to pull from the right row. There could be two rows or several. However, some fields will be the same (or should be the same everywhere) that can act as the key. ID is the primary key.
Thank you in advance for the help and any suggestions!
Solved! Go to Solution.
Hi @briangeneration ,
Here is my solution using the almighty multi-row formula. I've used three, one for each column.
Attached the workflow I've created.
Gabriel
Hi! Thank you for taking a look at helping. The answer screenshot (I got the same answer when I ran it) is not the screenshot I'm hoping to achieve. There is data in the wrong rows, and the rows aren't simplified. So neither is correct.
Again, example data:
ID | Gender | Age | Education | Special answer 1 | Special answer 2 | Special answer 3 |
1 | M | 20 | College | A | B | [Null] |
1 | M | 20 | College | [Null] | [Null] | C |
1 | M | 20 | College | [Null] | [Null] | [Null] |
1 | M | 20 | College | [Null] | [Null] | [Null] |
2 | F | 25 | High School | [Null] | B | [Null] |
2 | F | 25 | High School | D | [Null] | F |
3 | M | 30 | College | A | [Null] | [Null] |
3 | M | 30 | College | [Null] | G | [Null] |
And hopeful outcome:
ID | Gender | Age | Education | Special answer 1 | Special answer 2 | Special answer 3 |
1 | M | 20 | College | A | B | C |
2 | F | 25 | High School | D | B | F |
3 | M | 30 | College | A | G | [Null] |
As you can see, for ID 1, A is the only answer that is not null for Special answer 1. So I should get A as the answer in the final line item. For ID 3, Special answer 3 should be [Null] since there is no data for that answer in any row for ID 3.
If you can guarantee that the Special Answer fields will only ever have one input and the rest are Nulls, you can do this with a simple summarize tool like below.
Grabbing the Max value for the Special Answer fields will work because a value will always be greater than a Null.
If however your data is a little more complicated, we can use a Transpose/Crosstab method.
Here is it flipping your data, sorting it, copying the values down, flipping back, and finally finding only instances of unique values within the Special Answers fields.
Attached is a sample workflow with both methods for you to try.
Cheers!
Phil
Hi @briangeneration,
You can just transpose the data, filter out nulls and then crosstab back to your desired layout.
Regards,
Ben