merge cell values if common value in adjacent cell

[shumonsaha]

I have data in two columns

Column A has values with names like -

John, Paul, Hena, Kumar, Paul, Hena, John in different rows

Colum B has day values - eg , sun, mon, tue, wed , thu, fri and sat

I want to insert a new column C with formula that returns

1. for every John, concatenate of all the day values adjacent to row of John eg "sunmonfri"

Similarly for all the values in Column A, Column C should reflect the concatenate values of Column B.

Please help

 

[Aladin Akyurek]

Thanks Aladdin. That was too good.

Maybe asking for too much.. but any way I can get it to revert with days in serial order eg. sun mon tue wed thu fri sat

Currently its all jumbled up..

C2:

Control+shift+enter, not just enter...

=MCONCAT(LOOKUP(UNIQUEVALUES(
    IF($A$2:$A$8=A2,LOOKUP($B$2:$B$8,
      {"Fri",6;"Mon",2;"Sat",7;"Sun",1;"Thu",5;"Tue",3;"Wed",4}),""),1),
    {"","";1,"Sun";2,"Mon";3,"Tue";4,"Wed";5,"Thu";6,"Fri";7,"Sat"}))