Excel: Finding unique values and inserting them in the first row for each user occurance


I have a spread sheet with users' permissions and permission categories in two different columns. There are 200 permissions falling into 5 broad permission categories. Therefore, for each user my spread sheet currently looks like this:

User Permission Permission Category
User1 trade stocks buy-sell
User1 import items buy
User1 issue orders sell
User2 distribute items sell
User2 issue bills sell
User3 order items buy
User3 request delivery buy
I would like to add a new column to the above spread sheet to list all unique values for each user in first row when user name occurs first. The resultant spread sheet should look like this:
User Permission Permission Category Unique Permission Categories
User1 trade stocks buy-sell buy-sell, buy,sell
User1 import items buy
User1 issue orders sell
User2 distribute items sell sell
User2 issue bills sell
User3 order items buy buy
User3 request delivery buy
This allows me to easily see what unique permissions each user has rather than get confused when I see their permission in earch row of the permissions column. So basically, I need a formula or UDF that I would like to insert into the Unique-permission category column which will look at the user name and while the user name is the same it will look for all unique values in Permission Categories and list them in the last column (ignoring the duplicates). Any help would be highly appreciated. Thanks in advance.


This question generated 26 answers. To proceed to the answers, click here.

This thread is current as of January 03, 2014.


For more resources for Microsoft Excel