Find first of multiple strings in dynamic column range & set other cells above/below it to corresponding values

[chomsky123]

I have a formula to find dynamic ranges in a column "A" based on salesperson and then look in the adjacent column "C" for various sales years and then in column "E" I have to record years working for the company. The problem is column "C" is not filled out properly and many sales years are blank. I want to search down column "C" and when the first year is found then to populate the other cells in the dynamic range with the corresponding sales years, e.g. If I start the search at 1995 but the first year returned is 2003 I want to assign the cell directly above 2003 as 2002 and so on until the first row of the dynamic range and then to assign the rows below 2004, 2005 and so on until the final row of the dynamic range.

Then I will go to copy columns "A", "C" and "E" and paste them into a separate spreadsheet in the same workbook.

Are there any simple ways of going about doing this? I'm very much a noob, but learning. Any help would be greatly appreciated.


A B C D E
Bob 4
2003 5
2004 6
7
8
Alice 1
2
3
4
5
2000 6
7
8
9
Peter 3
4
5
6
2002 7
2003 8
2004 9
10
Amy 2

 

[chomsky123]

Sorry, this is how I should've sent the example data.

 

[chomsky123]

Imagine D is in E. The point is the columns are now contiguously connected in adjacent columns.