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

techie2013 · Jan 3, 2014

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.

hiker95 · Jan 6, 2014

techie2013,

With you raw data already sorted/grouped per your latest screenshot.

Sample raw data:

Excel 2007

A

B

C

D

E

F

G

H

I

1

UserID

GroupID

UserName

Topic

SubTopic

Function

FunctionID

TemplateAM

2

rgr1

NULL

Roger

Performance

Shift from door

ShiftDoor

TMPAM03

3

rgr1

NULL

Roger

Pepetation

Repeat steps

Repeator

TMPAM03

4

jst1

GroupB

Justin

Collection

Collect Coupons

Collector

TMPAM02

5

jst1

GroupB

Justin

Statistics

Data Analysis

Statician

TMPAM04

6

Sheet1

After the new macro:

Excel 2007

A

B

C

D

E

F

G

H

I

1

UserID

GroupID

UserName

Topic

SubTopic

Function

FunctionID

TemplateAM

Unique Template AM

2

rgr1

NULL

Roger

Performance

Shift from door

ShiftDoor

TMPAM03

3

rgr1

NULL

Roger

Pepetation

Repeat steps

Repeator

TMPAM03

4

jst1

GroupB

Justin

Collection

Collect Coupons

Collector

TMPAM02

TMPAM02, TMPAM04

5

jst1

GroupB

Justin

Statistics

Data Analysis

Statician

TMPAM04

6

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).

Code:

Option Explicit
Sub GetUniqueTemplateAMs()
' hiker95, 01/06/2014
' http://www.mrexcel.com/forum/excel-questions/748181-finding-unique-values-inserting-them-first-row-each-user-occurance.html
Dim lr As Long, r As Long, n As Long, c As Range, rng As Range
Application.ScreenUpdating = False
Columns(9).ClearContents
With Cells(1, 9)
  .Value = "Unique Template AM"
  .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, 9) = Cells(r, 8)
    ElseIf n > 1 Then
      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, 9) = Join(.Keys, ", ")
    End If
    r = r + n - 1
    .RemoveAll
  Next r
End With
Columns(9).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 GetUniqueTemplateAMs macro.

techie2013 · Jan 6, 2014

Hi hiker95
I am able to use (with slight modifications), the macro you provided to generate the result I was looking for. Now, I just need to include some code to order the results in the 'I' column of my 'After' spread sheet example above.

Here is the result what I am getting after running the macro:

A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>
UserID</SPAN>	GroupID</SPAN>	UserName</SPAN>	Topic</SPAN>	SubTopic</SPAN>	Function</SPAN>	FunctionID</SPAN>	TemplateAM</SPAN>	Unique Template AM</SPAN>
rgr1</SPAN>	NULL</SPAN>	Roger</SPAN>	Performance</SPAN>		Shift from door</SPAN>	ShiftDoor</SPAN>	TMPAM03</SPAN>	TMPAM03</SPAN>
rgr1</SPAN>	NULL</SPAN>	Roger</SPAN>	Pepetation</SPAN>		Repeat steps</SPAN>	Repeator</SPAN>	TMPAM03</SPAN>
jst1</SPAN>	GroupB</SPAN>	Justin</SPAN>	Collection</SPAN>		Collect Coupons</SPAN>	Collector</SPAN>	TMPAM02</SPAN>	TMPAM03,TMPAM02, TMPAM04</SPAN>
jst1</SPAN>	GroupB</SPAN>	Justin</SPAN>	Statistics</SPAN>		Data Analysis</SPAN>	Statician</SPAN>	TMPAM04</SPAN>
jst1</SPAN>	GroupB</SPAN>	Justin</SPAN>	Statistics</SPAN>		Data Analysis</SPAN>	Statician</SPAN>	TMPAM03</SPAN>

Notice that the resulting values in the last column ('I') are not in correct order. This is what I am looking for:

A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>
UserID</SPAN>	GroupID</SPAN>	UserName</SPAN>	Topic</SPAN>	SubTopic</SPAN>	Function</SPAN>	FunctionID</SPAN>	TemplateAM</SPAN>	Unique Template AM</SPAN>
rgr1</SPAN>	NULL</SPAN>	Roger</SPAN>	Performance</SPAN>		Shift from door</SPAN>	ShiftDoor</SPAN>	TMPAM03</SPAN>	TMPAM03</SPAN>
rgr1</SPAN>	NULL</SPAN>	Roger</SPAN>	Pepetation</SPAN>		Repeat steps</SPAN>	Repeator</SPAN>	TMPAM03</SPAN>
jst1</SPAN>	GroupB</SPAN>	Justin</SPAN>	Collection</SPAN>		Collect Coupons</SPAN>	Collector</SPAN>	TMPAM02</SPAN>	TMPAM02,TMPAM03, TMPAM04</SPAN>
jst1</SPAN>	GroupB</SPAN>	Justin</SPAN>	Statistics</SPAN>		Data Analysis</SPAN>	Statician</SPAN>	TMPAM04</SPAN>
jst1</SPAN>	GroupB</SPAN>	Justin</SPAN>	Statistics</SPAN>		Data Analysis</SPAN>	Statician</SPAN>	TMPAM03</SPAN>

For the user Justin and similar, I would like the values to be in correct serial order.

Can you please help. Thanks

hiker95 · Jan 6, 2014

techie2013,

Hi hiker95
I am able to use (with slight modifications), the macro you provided to generate the result I was looking for. Now, I just need to include some code to order the results in the 'I' column of my 'After' spread sheet example above.

The use of the Scripting.Dictionary is still new to me.

1. I could save the raw data in an array.
2. Then sort the raw data.
3. Then run the macro.
4. Then put the original data back without disturbing column I.

Does that sound OK?

hiker95 · Jan 6, 2014

techie2013,

With you raw data already sorted/grouped per your latest screenshot.

I found another workaround.

Sample raw data:

Excel 2007

A

B

C

D

E

F

G

H

I

1

UserID

GroupID

UserName

Topic

SubTopic

Function

FunctionID

TemplateAM

2

rgr1

NULL

Roger

Performance

Shift from door

ShiftDoor

TMPAM03

3

rgr1

NULL

Roger

Pepetation

Repeat steps

Repeator

TMPAM03

4

jst1

GroupB

Justin

Collection

Collect Coupons

Collector

TMPAM02

5

jst1

GroupB

Justin

Statistics

Data Analysis

Statician

TMPAM04

6

jst1

GroupB

Justin

Statistics

Data Analysis

Statician

TMPAM03

7

Sheet1

After the new macro:

Excel 2007

A

B

C

D

E

F

G

H

I

1

UserID

GroupID

UserName

Topic

SubTopic

Function

FunctionID

TemplateAM

Unique Template AM

2

rgr1

NULL

Roger

Performance

Shift from door

ShiftDoor

TMPAM03

3

rgr1

NULL

Roger

Pepetation

Repeat steps

Repeator

TMPAM03

4

jst1

GroupB

Justin

Collection

Collect Coupons

Collector

TMPAM02

TMPAM02, TMPAM03, TMPAM04

5

jst1

GroupB

Justin

Statistics

Data Analysis

Statician

TMPAM04

6

jst1

GroupB

Justin

Statistics

Data Analysis

Statician

TMPAM03

7

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).

Code:

Option Explicit
Sub GetUniqueTemplateAMsV2()
' hiker95, 01/06/2014
' http://www.mrexcel.com/forum/excel-questions/748181-finding-unique-values-inserting-them-first-row-each-user-occurance.html
Dim lr As Long, r As Long, n As Long, c As Range, rng As Range, h As Variant
Application.ScreenUpdating = False
Columns(9).ClearContents
With Cells(1, 9)
  .Value = "Unique Template AM"
  .Font.Bold = True
End With
lr = Cells(Rows.Count, 1).End(xlUp).Row
h = Range("H2:H" & lr)
With CreateObject("Scripting.Dictionary")
  For r = 2 To lr
    n = Application.CountIf(Columns(1), Cells(r, 1).Value)
    If n = 1 Then
      Cells(r, 9) = Cells(r, 8)
    ElseIf n > 1 Then
      Range("H" & r & ":H" & 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, 9) = Join(.Keys, ", ")
    End If
    r = r + n - 1
    .RemoveAll
  Next r
End With
Columns(9).AutoFit
Range("H2:H" & lr) = h
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 GetUniqueTemplateAMsV2 macro.

techie2013 · Jan 6, 2014

Yup. It works like a charm. Thank you so much. This solved my problem. In fact I used the same macro with modifications twice to create additional column of information.

I really appreciate all your help and your patience. Have a great day!

hiker95 · Jan 6, 2014

techie2013,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

techie2013 · Jan 7, 2014

Hi hiker95
In fact I do need further help. As I mentioned to you before, I had further modified your latest macro in #14 as follows:

Code:

Option Explicit
Sub GetUniqueTemplateAMsV2()
' hiker95, 01/06/2014
' [URL]http://www.mrexcel.com/forum/excel-questions/748181-finding-unique-values-inserting-them-first-row-each-user-occurance.html[/URL]
Dim lr As Long, r As Long, n As Long, c As Range, rng As Range, h As Variant
Application.ScreenUpdating = False
Columns(11).ClearContents
With Cells(1, 11)
  .Value = "Access Models"
  .Font.Bold = True
End With
lr = Cells(Rows.Count, 1).End(xlUp).Row
h = Range("H2:H" & lr)
With CreateObject("Scripting.Dictionary")
  For r = 2 To lr
    n = Application.CountIf(Columns(1), Cells(r, 1).Value)
    If n = 1 Then
      Cells(r, 11) = Cells(r, 9)
    ElseIf n > 1 Then
      Range("H" & r & ":H" & 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, 11) = Join(.Keys, ", ")
    End If
    r = r + n - 1
    .RemoveAll
  Next r
End With
Columns(11).AutoFit
Range("H2:H" & lr) = h
Application.ScreenUpdating = True
End Sub

I got the following result:

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

Notice that when a user (eg Peter) has only one AccessModel in column H, the macro leaves the column J blank. What I expect is to insert 'Access Model3'. This issue is with all users that have only one items.

Can you possibly further modify the macro in order to fix that issue.

Thanks and have a great day!

hiker95 · Jan 7, 2014

techie2013,

With you raw data already sorted/grouped per your latest screenshot.

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).

Code:

Option Explicit
Sub GetUniqueHI()
' hiker95, 01/07/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
hi = Range("H2:I" & lr)
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)
    ElseIf n > 1 Then
      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, ", ")
      .RemoveAll
    End If
    r = r + n - 1
  Next r
End With
Columns("J:K").AutoFit
Range("H2:I" & lr) = hi
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 macro.

techie2013 · Jan 8, 2014

Hi hiker95
This is so awesome. This macro solved two problems. I am running one macro instead of two! Also, the issue with cells not receiving values is resolved. Thank you so much!!

There is another twist to this which is not so important but if it can be easily done then it will help the user of this report tremendously.

The report user would not like to see the information in H and I columns in the first row for each UserID. Can the some additional code be added at the end of the GetUniqueHI() macro to remove the entries in those two cells on the first line for each user. See example of the final result they would like to see.

PS: The macro will not be saved on the spread sheet

After:
A	B	C	D	E	F	G	H	I	J	K
UserID	GroupID	UserName	Topic	SubTopic	Function	FunctionID	NA	NA	Access Models	Entitlements
rgr1	NULL	Roger	Performance		Shift from door	ShiftDoor			AccessModel1	TMPAM03
rgr1	NULL	Roger	Pepetation		Repeat steps	Repeator	AccessModel1	TMPAM03
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

If this is making is complex then no worries. We are still good. Thanks again for all your help.

hiker95 · Jan 8, 2014

techie2013,

Please supply another screenshot where there are three rows for rgr1.

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

techie2013

New Member

hiker95

Well-known Member

Excel Facts

techie2013

New Member

hiker95

Well-known Member

hiker95

Well-known Member

techie2013

New Member

hiker95

Well-known Member

techie2013

New Member

hiker95

Well-known Member

techie2013

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

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

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock