UDF to lookup and return multiple values concatenated into one cell without duplicates

[rhaas128]

I am looking at a few thousand rows and need to locate the matches, remove duplicates for each, and concatenate the remaining values into a single cell with a delimiter. Example:


The server list column should be compared against another sheet that contains a list of server names and owners. If multiple matching server names are found, the owners in the owner name column should be concatenated with a comma and put into the owner list.

If a a server name and owner are duplicates, they should be ignored.

So below, Server01p01 is listed 3 times, twice with UserA, and once with UserB as the owner. The second UserA should be ignored as it has a matching pair already, and only UserA and UserB should be added to the owner list.



[TABLE="width: 500"]
<tbody>[TR]
[TD]Server List[/TD]
[TD]Owner List[/TD]
[/TR]
[TR]
[TD]Server01p01[/TD]
[TD]UserA, UserB[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]UserB[/TD]
[/TR]
[TR]
[TD]Server94_S02[/TD]
[TD]UserA[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500"]
<tbody>[TR]
[TD]Server Name[/TD]
[TD]Owner Name[/TD]
[/TR]
[TR]
[TD]Server01p01[/TD]
[TD]UserA[/TD]
[/TR]
[TR]
[TD]Server2[/TD]
[TD]UserB[/TD]
[/TR]
[TR]
[TD]Server01p01[/TD]
[TD]UserB[/TD]
[/TR]
[TR]
[TD]Server94_S02[/TD]
[TD]UserA[/TD]
[/TR]
[TR]
[TD]Server01p01[/TD]
[TD]UserA[/TD]
[/TR]
</tbody>[/TABLE]

Any and all help is greatly appreciated. Thanks in advance!

 

[rhaas128]

So far so good, and much appreciated it. On some results I am getting a 0 instead of errors or #n/a. Any thoughts how to fix? I assume a nested IF, but not sure. I know that the entries exist, just not getting the correct data back.

 

[Fluff]

That suggests that there is a problem with the data, check for leading/trailing spaces etc.