-1

I have two columns of data. Some of the data in the first column repeats (they represent questions). The data in the second column is unique (they represent multiple answers to the same question).

I need to merge all the data in the second column for each unique value in the first column. e.g.:

Q,A
1,yes.
1,is possible.
2,no.
2,not possible.
2,cannot do this.
2,impossible.
3,maybe.

merged to:

Q,A    
1,yes.is possible.
2,no.not possible.cannot do this.impossible.
3,maybe.
pnuts
  • 58,317
  • 11
  • 87
  • 139
John
  • 241
  • 1
  • 6
  • 1
    Welcome to StackOverflow. To increase you chance of answer, you have to show some research effort: What have you tried ? Where did you fail ? – achedeuzot Jul 11 '14 at 22:27

3 Answers3

1

Something like this is crude but may be adequate: 

 =IF(A1=A2,C1&B2,B2)

copied down to suit. Then select the last entry (identifiable with something like =A1=A2 copied down to suit) for each Question number.

pnuts
  • 58,317
  • 11
  • 87
  • 139
0

Questions in column A sorted in order

Answers in column B

In C1 use =B1

In C2 use =if(a2=A1,C1&B2,B2)

Drag down formula in C2.

It will keep adding the lines together as long as the question remains the same. When it gets to a new question, it'll start a new string. The last time each question is listed will be the complete string in column C.

ScotBirmingham
  • 50
  • 5
  • This seems to just repeat the 1st value, not combine them. – John Jul 14 '14 at 17:14
  • When you say it just repeats the first value, what do you mean? Based on your example C2 says `yes.` C3 also says `yes.`? Also, I fail to see how you get `0` when you try pnuts solution and something different when you try mine since we both said the exact same thing. I think you need to provide a little more feedback if you want more help. – ScotBirmingham Jul 14 '14 at 22:36
  • Apologies for my terse comments, they were honest observations. I am sure I did something wrong. I have solved this task using Open/Google Refine. Thank you for assisting me! – John Jul 15 '14 at 17:05
-1
  1. Create a 2 column project in Google Refine
  2. Sort by Q column (if not already sorted) and make sort permanent
  3. Blank Down on Q column to remove duplicate values
  4. On A column, do Edit Cells -> Merge multi-valued cells
John
  • 241
  • 1
  • 6