excel - Concatenate values based on criteria

Question

I have a two column list of data in Excel. The first column being a question number from a test and the second column being a number referencing what is being tested on that question. Some elements are tested on more than one question. What I want to be able to do is to list the question numbers that each element is tested on. For example:

   A            B      Should return:   C            D

1  Q            Ref                     Q            Ref
2  1            N1                      1,3,5        N1
3  2            N4                      2            N4
4  3            N1                      4            N3
5  4            N3
6  5            N1

I want this to be returned using a formula.

Problems I have are returning then concatenating an unspecified number of values from one column that reference to a particular criterion for another column that is further to the right.

EDIT: Looking for a formula answer, not VBA if possible

EDIT: Thanks all for your comments so far. I will have a look at each of the possible solutions given so far and let you know what I go with. The 1,2,3 etc will need to be in the same cell.

Answer 1

Just to put my comment in an answer, so it make more sense.

First sort columns A and B on Column B.

In C2 put the formula:

=IF(B2=B3,A2&","&C3,A2)

Then copy down.

Then in Column E place your unique reference list. And in D2 put:

=VLOOKUP(E2,$B$2:$C$6,2,FALSE)

And copy down.

You can then hide column C.

It does require that it be sorted correctly and a helper column but it does stay to the formulas only rule.