Excel formula to create one row for each subject, and replace missing value in a column with the next available value for a particular subject

I have a table as below,

ID	Name	2011	2012	2013	2014
A1	BOB				Y
A1	BOB		Y
A1	BOB
A1	BOB	Y
B1	SAM
B1	SAM
B1	SAM			Y	Y
B1	SAM
B1	SAM		Y
C1	TOM			Y
C1	TOM
C1	TOM
C1	TOM			Y
C1	TOM	Y
C1	TOM	Y		Y
---	----	------	----	------	----

I want to create a table as shown below. That is, only one final row for each ID, and if any row of that ID has "Y" in a particular year, the final row will have an "Y"

ID	Name	2011	2012	2013	2014
A1	BOB	Y	Y		Y
B1	SAM		Y	Y	Y
C1	TOM	Y		Y
---	----	------	----	------	----

I googled but failed to find an answer