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

[techie2013]

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

<TBODY>
</TBODY>

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

<TBODY>
</TBODY>

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.

 

[techie2013]

Here you go..

After:
A	B	C	D	E	F	G	H	I	J	K
UserID	GroupID	UserName	Topic	SubTopic	Function	FunctionID			Access Models	Entitlements
rgr1	NULL	Roger	Performance		Shift from door	ShiftDoor			AccessModel1, AccessModel2,AccessModel4	TMPAM01, TMPAM02, TMPAM04
rgr1	NULL	Roger	Pepetation		Collect coupons	Collector	AccessModel2	TMPAM02
rgr1	NULL	Roger	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
jst1	GroupB	Justin	Collection		Collect coupons	Collector			AccessModel2,AccessModel4	TMPAM02, TMPAM04
jst1	GroupB	Justin	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
ptr1	GroupC	Peter	Recreational		Take rides	Funlover			AccessModel3	TMPAM03

<TBODY>
</TBODY>

The grey color in the blank cells in the above example is just to identify them to be the cell that will not have values after the macro is run. They can have white background.

 

[hiker95]

techie2013,

I am starting to get confused.

Can I have a before screenshot, of the after screenshot in your reply #21?

 

[techie2013]

Oh, sorry to cause any confusion. In face I changed data to make it more clear. Here is the 'Before' spread sheet for the one 'After' spreadsheet in #21:

Before:
A	B	C	D	E	F	G	H	I	J	K
UserID	GroupID	UserName	Topic	SubTopic	Function	FunctionID
rgr1	NULL	Roger	Performance		Shift from door	ShiftDoor	AccessModel1	TMPAM01
rgr1	NULL	Roger	Pepetation		Collect coupons	Collector	AccessModel2	TMPAM02
rgr1	NULL	Roger	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
jst1	GroupB	Justin	Collection		Collect coupons	Collector	AccessModel2	TMPAM02
jst1	GroupB	Justin	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
ptr1	GroupC	Peter	Recreational		Take rides	Funlover	AccessModel4	TMPAM03

<TBODY>
</TBODY>

The macro will work in the same way as before (insert unique values in column J and K). Additionally, it needs to clear values from the cells that are greyed out in the 'After' example on #21. Please let me know if you need more info.

 

[hiker95]

techie2013,

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	UserID	GroupID	UserName	Topic	SubTopic	Function	FunctionID
2	rgr1	NULL	Roger	Performance		Shift from door	ShiftDoor	AccessModel1	TMPAM01
3	rgr1	NULL	Roger	Pepetation		Collect coupons	Collector	AccessModel2	TMPAM02
4	rgr1	NULL	Roger	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
5	jst1	GroupB	Justin	Collection		Collect coupons	Collector	AccessModel2	TMPAM02
6	jst1	GroupB	Justin	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
7	ptr1	GroupC	Peter	Recreational		Take rides	Funlover	AccessModel4	TMPAM03
8
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	UserID	GroupID	UserName	Topic	SubTopic	Function	FunctionID			Access Models	Entitlements
2	rgr1	NULL	Roger	Performance		Shift from door	ShiftDoor			AccessModel1, AccessModel2, AccessModel4	TMPAM01, TMPAM02, TMPAM04
3	rgr1	NULL	Roger	Pepetation		Collect coupons	Collector	AccessModel2	TMPAM02
4	rgr1	NULL	Roger	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
5	jst1	GroupB	Justin	Collection		Collect coupons	Collector			AccessModel2, AccessModel4	TMPAM02, TMPAM04
6	jst1	GroupB	Justin	Statistics		Data analysis	Statician	AccessModel4	TMPAM04
7	ptr1	GroupC	Peter	Recreational		Take rides	Funlover			AccessModel4	TMPAM03
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub GetUniqueHI_Final()
' hiker95, 01/08/2014, ME748181
Dim lr As Long, r As Long, n As Long, c As Range, rng As Range, hi As Variant
Application.ScreenUpdating = False
Columns("J:K").ClearContents
With Cells(1, 10).Resize(, 2)
  .Value = Array("Access Models", "Entitlements")
  .Font.Bold = True
End With
lr = Cells(Rows.Count, 1).End(xlUp).Row
With CreateObject("Scripting.Dictionary")
  For r = 2 To lr
    n = Application.CountIf(Columns(1), Cells(r, 1).Value)
    If n = 1 Then
      Cells(r, 10) = Cells(r, 8)
      Cells(r, 11) = Cells(r, 9)
      Cells(r, 8).Resize(, 2).ClearContents
    ElseIf n > 1 Then
      hi = Range("H" & r & ":I" & r + n - 1)
      Range("H" & r & ":I" & r + n - 1).Sort key1:=Range("H" & r), order1:=1
      Set rng = Range("H" & r & ":H" & r + n - 1)
      For Each c In rng
        If c <> "" Then
          If Not .Exists(Trim(c.Value)) Then
            .Add c.Value, c.Value
          End If
        End If
      Next c
      Cells(r, 10) = Join(.Keys, ", ")
      .RemoveAll
      Set rng = Range("I" & r & ":I" & r + n - 1)
      For Each c In rng
        If c <> "" Then
          If Not .Exists(Trim(c.Value)) Then
            .Add c.Value, c.Value
          End If
        End If
      Next c
      Cells(r, 11) = Join(.Keys, ", ")
      Range("H" & r & ":I" & r + n - 1) = hi
      Range("H" & r & ":I" & r).ClearContents
      Erase hi
      .RemoveAll
    End If
    r = r + n - 1
  Next r
End With
Columns("J:K").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniqueHI_Final macro.

 

[techie2013]

Perfect! And yes, it is final indeed. Works great and does everything the user needs to get done. Terrific job. Thank you again for the help.

Have a great day!
techie2013

 

[hiker95]

techie2013,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.