If you look at the example Excel I've attached I've tried to explain what I am trying to do but have no idea how to write the macro to accomplish the task.
If you review tab name Starting Sheet I have tried to show what I need done. This is a very small representation. The real Excel file has over 500 rows.
If you review the tab named Result I've tried to illustrate what the end result needs to look like.