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 )

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.