Hi! I hope someone can help me simplify my data. Currently, I have data that looks like this (example): IDGenderAgeEducationSpecial answer 1Special answer 2Special answer 31M20CollegeAB[Null]1M20College[Null][Null]C1M20College[Null][Null][Null]1M20College[Null][Null][Null]2F25High School[Null]B[Null]2F25High SchoolD[Null]F3M30CollegeA[Null][Null] 3M30College[Null]G[Null] I want it to look like this: IDGenderAgeEducationSpecial answer 1Special answer 2Special answer 31M20CollegeABC2F25High SchoolDBF3M30CollegeAG[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!

Combining data in similar rows

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!

Datasets

Tips and Tricks

Common Use Cases

Join

Accepted answers

Maskell_Rascal

Hi @briangeneration

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

Combining data in similar rows.yxmd

All comments

Garabujo7

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

combining data.yxzp

briangeneration

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.

Maskell_Rascal

Hi @briangeneration

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

Combining data in similar rows.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167