How to concatenate a dynamic list of cells but only concatenate unique entries

[Philster]

Hi, all

I have a number of worksheets. Each worksheet has rows, each row with a person's name on it. Just to the right of their name is a cell "Date Last Reviewed yyyy-mm-dd" which they update once they have updated the info in the row. Essentially they are checklists.

OK, so now I want to build a status of who still has outstanding rows to review. I have another worksheet called Status. Each row contains the name of a checklist above in a column "Name".

There is a column called "Outstanding" that I'd like to have the names of the people who date last reviewed is at least a year old. For example, the result would be "Joe Schmoe, John Doe, Mary Moe", meaning that each of them has at least 1 date over 1 year old. If Joe has more, for example, his name only appears once.

I'd prefer a formula, if possible, please.

[tony h]

it would be easier if you attached a sample workbook

[Philster]

Your wish is my command.

In this example there are 2 worksheets. The first, Checklist-DRP Deps, shows a list of checklists. The second one, App Protection Activities, is a checklist of application protection activties, and is one of the checklists described in the list of checklists.

In cell E8, I have put what the results should be. I need a formula to replace this. Namely Excel should, for each row in worksheet App Protection Activities with column DRP entries="BBT SMB DRP", return the corresponding value in DR Team Role Responsible that have column Last Performed yyyy-mm-dd that are null (not reviewed), or older than a year old. It should make the results unique, so that if a name is mentioned more than once, only one occurence is returned.

I hope it's clearer with the example. I am hoping to use so that I can add to the second list, and have Excel show me who is due or not.