return unique values in a table with 2 columns using excel formulas

I was searching the net and the stackoverflow but couldnt figure it out. =(

My sheet looks like:

R1  U1
R1  U1开发者_运维百科
R1  U2
R1  U3
R2  U2
R2  U2
R2  U3

Is it possible to use a excel formula to return

R1 U1,U2, U3
R2 U2, U3

Thanks!

As far as I know, there are no functions in Excel which will do this. What you are wanting is essentially a VLOOKUP that returns all values instead of one.

However, you can write a VBA function to do this, similar to:

Public Function FindSeries(TRange As Range, MatchWith As String) 

    For Each cell In TRange 
        If cell.Value = MatchWith Then 
            x = x & cell.Offset(0, 1).Value & ", " 
        End If 
    Next cell 

    FindSeries = Left(x, (Len(x) - 2)) 

End Function 

You can find this code and more information here: http://www.ozgrid.com/forum/showthread.php?t=25239&page=1

a pivot table

Assume the cell you are entering R1 or R2 into is a named range clVal. If your data as above is in the range A1:B7, add a helper column in C1:C7 as follows:

C1:  =IF($A$1=clVal,B1 & ", ","")
C2:  =IF($A2=clVal,$C1&IF(ISERROR(FIND($B2,$C1)),B2&",",""),$C1)
 copy this formula down to C7.

Finally, we need to chop of the training comma if there is one. This can be done with

=LEFT(C7,LEN(C7)-1*(RIGHT(C7,1)=","))