Merge unique values if another cell matches

Question

Merge all unique values if another cell matches. I already know how to merge cells but now some information is double. So what I would like to achieve is the following:

if column A has the same name, then all values given in column B for that name must be given only ONCE in a new column.

My data has a row names and a row mode, for example (Row 1 is header)

     A      B    
2  Brenda   a
3  Brenda   a
4  Joey     a
5  Joey     b

So I want:

     E      
2    a
3  
4   a,b
5  

I already did merge the modes in column 3:

=IF(A1<>A2;B2;C1&","&B2)

So I get in this example:

     C      
2    a
3   a,a
4    a
5   a,b 

Then, I already did that only the first record get the additional modes in column 4:

=IF(A1=A2;"";INDEX(Sheet1!$C:$C;COUNTIF(Sheet1!$A:$A;$A2)+MATCH($A2;Sheet1!$A:$A;0) -1))

So I get in this example

     D      
2    a,a
3  
4    a,b
5  

Now I need a column that only uniques values are given for each name. So in this example:

     E      
2    a
3  
4    a,b
5  

Answer 1

If I am understanding how your data is structured, try this:

Add a new column, say column G for ease of explanation, that concatenates the name and mode in each row. So, cell G2="Brendaa", G3="Brendaa", G4="Joeya", G5="Joeyb", etc.

In your merge step you will test whether the current value in the cell for this column matches any previous values in the column: If no, you do the merge; if yes, you don't.

Your merge formula would change to something like the following:

    =IF(A1<>A2,B2,IF(ISERROR(VLOOKUP(G2,G$1:G1,1,0)),C1&","&B2,""))

Then you would the next step as before.