column has gaps; need to fill rows with data, based on another column

Hello,

2 issues: will draw sample data below so hopefully it makes sense. Note: in the format below -- is the column separator for columns A and B.

A -- B
Mike -- 1234
Mike --
Sanjay --
Sanjay -- 1456
Sanjay --
Ted --
Ted --
Chris --
Chris -- 4444


in the above example, this is what the end result should be:

A -- B
Mike -- 1234
Mike -- 1234
Sanjay -- 1456
Sanjay -- 1456
Sanjay -- 1456
Ted --
Ted --
Chris -- 4444
Chris -- 4444

In the above example, column B for the TED rows are still blank, because NONE of the TED rows, have a value, so the source data is blank for TED, so that's fine. Basically if the source data has a value for any of the rows, all the rows with the same column A value, should have the same B value.

The next part is a bit stranger. Vlookups and matches aren't that difficult, but how do we limit it based on a section of data.


Example:

A -- B -- C
123 -- Mike -- Chris
123 -- Susan -- Lin
567 -- Sanjay -- Ted
567 -- Kary -- Alice
567 -- Alice -- Opum

In the above case, column D should result no match for records under A = 123, since the names in B and C dont have any matches, but for 567, column D should show: 567 Alice

since alice is in both columns B and C.

Thanks

Regards,

Sanjay

PS: i can't think how to do this with regular functions; i assume array formulaes, which i just dont get. is anyone here, that can explain arrow formulae if i send a bunch of questions? the help files aren't really helping (I used those to learn the regular functions and was fine but something about arrays is much worse )