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!

[Qiu]

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

[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...

[binuacs · Accepted solution]

@jt1986 

[jt1986]

@binuacs

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!