Count rows between repeating values

[msquared99]

In the example spreadsheet in column A is an ID# and in column B is the relationship. In row 2 there is the employee column B and their ID# in column A. In rows 3-5, they are related to the employee in row 2. What I am trying to do is in column C to have the ID# repeat until Employee is found again (Row6). Once Employee is found again then the new ID# will be put in row 6.

In the uploaded file I have the Example and the Desired Result.

Thanks!

 

[Chihiro]

Try...
=INDEX($A$2:A2,LARGE(IF($B$2:B2="Employee",ROW($A$2:A2)-1),1))

Confirmed as array (CTRL + SHIFT + ENTER)

Copy down.

 

[r2c2]

@msquared99

While @Chihiro 's formula works, you could get the same result with a simple IF formula too, like this:

assuming your want the result in column C:

=IF(B2="Employee",A2,C1)

 

[Chihiro]

Ah, right Should have thought of that one.

 

[msquared99]

Thank for both replies. Chihiro's formula delivered the desired result. I had almost the same formula but used TRUE after INDEX. That was what threw me off.

Again, thanks to both of you.