How to find next row that matches current row (based on condition) then paste value from another column?

Say I have some data that looks like this

Name     Type   Rating

Dave     Good   3.0
Steve    Bad    0.0
Steve    Good   2.0
Dave     Bad    1.0
Tom      Bad    2.0
Marianne Good   0.0
Tom      Bad    1.0
Steve    Bad    5.5
Marianne Bad    3.0

I want to take rows where 'Type' matches a certain value (i.e 'Good') then find the next row where the associated name matches the current row (i.e if the name next to 'Good' is 'Dave', find the next row where the name is also 'Dave') and then paste the value in the 'Rating' column to a new column.

The output should look something like this.

Name     Type   Rating  New

Dave     Good   3.0     1.0
Steve    Bad    0.0
Steve    Good   2.0     5.5
Dave     Bad    1.0
Tom      Bad    2.0
Marianne Good   0.0     3.0
Tom      Bad    1.0
Steve    Bad    5.5
Marianne Bad    3.0

Thanks for any help!