How can I find and group unique combinations

[sbiitmcdk]

I am really stock in this

I have a list of users and their privilege represented by a value in this example setup.

I have then made a pivot to group the privileges by the users to get an overview. But what I really need is to show how many unique combinations based on users there are in my table.

In the given example we have the combination of 1,2,3 and 4 on user A, so that is one unique combination. Now I need to know how many users (and who) shares this unique combi.

Again user B has a different unique combination, who else have that?

etc.

My data is 44000 rows with 1900 different users and 150 different privileges. So it's going to be a very long night...

 

[AlphaFrog]

This is a little tricky. There may be a more elegant way to do this, but this is what I could come up with.

I used an abbreviated list of your data for this example.

Make a Pivot Table of your raw data from columns A and B where User is in the row area and Privilege is in the column area

It would look something like this (except many more columns)

Excel Workbook
	A	B	C	D
14	Sum of Privilege	Privilege
15	User	1	2	3
16	A	1	2	3
17	B	1	2	3
18	C	1	2
19	D	1		3
...

For each user, you should have a list of their privileges. In the next empty column to the right of that pivot table, put this formula (column E in this example)
=MCONCAT(B15:D15, ",")
and copy it down the column for each user. Give this column the Header Privileges.

MCONCAT is a custom function that concatinates a range of cells. You can see the descriptions for it at Morefunc add-in Download: Morefunc.

Then you should have something like this...

Excel Workbook
	A	B	C	D	E
15	User	1	2	3	Privilages
16	A	1	2	3	1,2,3
17	B	1	2	3	1,2,3
18	C	1	2		1,2,
19	D	1		3	1,3
...

Now select all the data in the pivot table including the Privileges column (A15:E19) and make a second pivot table from that data where Privileges is in the Row area, User is in the column Area, and Count of Privileges is in the data Area. It should look something like this...

Excel Workbook
	G	H	I	J	K	L
15	Count of Privilages	User
16	Privilages	A	B	C	D	Grand Total
17	1,2,3	1	1			2
18	1,2,			1		1
19	1,3				1	1
...

 

[AlphaFrog]

Update!

A better MCONCAT formula would be
=TRIM(MCONCAT(B16:D16," "))
...to eliminate any trailing delimiters.

 

[sbiitmcdk]

Thanks for the fast reply.

I cannot get it to work in the first step, because when I make a pivot with users and privileges what I get in the value area is the sum of user/privilege which is the number '1'.

So user will have a lot '1' presented along the row.

For the example it's maybe better to use these text strings as privileges instead of numbers: QAZ, WSX, EDC, RFV.

Thanks a lot for the effort.

/SBI