Hi all, I have a problem where I want to combine data for some IDs which is currently in the following format: ID1Question 1Answer 1ID1Q2A2ID1Q3A3ID2Q1A1ID2Q2A2ID2Q3A3ID2Q4A4ID2Q5A5 What I would like to achieve is the following: IDQ1Q2Q3Q4ID1A1 for ID 1A2 for ID1A3 for ID1BlankID2A1 for ID 2A2 for ID2A3 for ID2A4 for ID2 So questions 1 to 3 are asked both for ID1 and ID2, I want to get those questions in the column header and the answers to them in the rows. For ID2 2 additional questions were asked, so I want them also in there, with blank cells for ID1 since these questions were not asked for this ID. Does anyone know how to solve this? Your help is much appreciated!

Combining data for the same ID on different rows into 1 row

jt1986

Hi all, I have a problem where I want to combine data for some IDs which is currently in the following format:

ID1	Question 1	Answer 1
ID1	Q2	A2
ID1	Q3	A3
ID2	Q1	A1
ID2	Q2	A2
ID2	Q3	A3
ID2	Q4	A4
ID2	Q5	A5

What I would like to achieve is the following:

ID	Q1	Q2	Q3	Q4
ID1	A1 for ID 1	A2 for ID1	A3 for ID1	Blank
ID2	A1 for ID 2	A2 for ID2	A3 for ID2	A4 for ID2

So questions 1 to 3 are asked both for ID1 and ID2, I want to get those questions in the column header and the answers to them in the rows. For ID2 2 additional questions were asked, so I want them also in there, with blank cells for ID1 since these questions were not asked for this ID.

Does anyone know how to solve this? Your help is much appreciated!

Preparation

Accepted answers

binu_acs

@jt1986

answers.yxmd

All comments

Qiu

@jt1986
We can use a CrossTab to do it if I understand correclty.

0317-jt1986.yxmd

jt1986

Thanks for the quick reply! I don't want the exact text "A1 for ID1" to appear. What I meant is that on my initial row 1, there is this first question with an answer for ID1, that answer could be anything. On row 4 is the same question asked for ID2 with a different answer. So where the end result now says "A1 for ID1", I want to have the answer there in the third column first row of my initial data. Where the end result now says "A1 for ID2", I want to have the answer there in the third column fourth row of my initial data. Hope this clears things up a bit...

jt1986

Oh no, its a little bit different, I've made an error in my initial picture. My initial table is not in split columns for Questions and answers, but is like this:

ID1	Question 1
ID1	Answer 1
ID1	Q2
ID1	A2
ID1	Q3
ID1	A3
ID2	Q1
ID2	A1
ID2	Q2
ID2	A2
ID2	Q3
ID2	A3
ID2	Q4
ID2	A4
ID2	Q5
ID2	A5

Sorry for creating the confusion...

Thanks a lot for helping! In this case the questions that are asked don't start with a Q, so that didn't work for me, however all questions do end with a question mark. That brought me to the idea to reverse your solution a little bit and so I used the formula "EndsWith([Field2], '?')" in the Filter tool. And then your solution works for me! So thanks a lot for helping out!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335