can there is any function in excel modify table a to table b plz suggest.

table a table b
no dept no dept
1 a 1 a,c,d
1 c -------- 2 e,f
1 d
2 e
2 f

thanks