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

rhaas128

Board Regular
Joined
Jul 5, 2013
Messages
84
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.



Server ListOwner List
Server01p01UserA, UserB
Server2UserB
Server94_S02UserA

<tbody>
</tbody>



Server NameOwner Name
Server01p01UserA
Server2UserB
Server01p01UserB
Server94_S02UserA
Server01p01UserA

<tbody>
</tbody>

Any and all help is greatly appreciated. Thanks in advance!
 
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.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
That suggests that there is a problem with the data, check for leading/trailing spaces etc.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top