Unique values with Multiple Criteria

jjacker · Apr 19, 2019

Hi Excel Masters,

I just need your assistance on how to answer the 'CourseAccessed' column from my table, wherein it will put 1 for each Unique 'Username' accessed(TimesAccessed) a Unique course in 'CourseTitle' column whatever Lesson(LessonNumber) it is, as long as he accessed the course.

Here is the table of my example:

Username	CourseTitle	LessonNumber	TimesAccessed	CourseAccessed
Peter	Excel 101	Lesson 1	2
Peter	Excel 101	Lesson 2	2
Peter	Working with Charts	Lesson 1	0
John	Excel 101	Lesson 1	11
John	Excel 101	Lesson 2	11
John	Excel 101	Lesson 3	11
John	Working with Charts	Lesson 1	5
John	Working with Charts	Lesson 2	5
Fred	Excel 101	Lesson 1	0
Fred	Excel 101	Lesson 2	0
Fred	Working with Charts	Lesson 1	2
Daniel	Excel 101	Lesson 1	4
Daniel	Excel 101	Lesson 2	3
Daniel	Excel 101	Lesson 3	2
Robert	Excel 101	Lesson 1	1
Robert	Working with Charts	Lesson 1	4
Robert	Working with Charts	Lesson 2	0
Ryan	Excel 101	Lesson 1	1
Ryan	Excel 101	Lesson 2	0

My idea here is to create a pivot table, and sum the number of users who accessed the courses. So I will be able to see the trending course accessed by the users.
And yes, the 'CourseAccessed' is a helper column so I can do the counting.

If you have better idea, please feel free to suggest. Your help is much appreciated. .

Thanks in Advance. More powers to your site.

Peter Bartholomew · Apr 19, 2019

Still futures but, when it is released, dynamic ranges will offer a simple clean alternative to pivot tables.
First a list of unique combinations of Username and CourseTitle is provided by
= UNIQUE( Accessed[[Username]:[CourseTitle]] )

Naming the columns of the new list, and using them as the criteria for an array of SUMIFS
= SUMIFS(Accessed[TimesAccessed], Accessed[Username], Username, Accessed[CourseTitle], CourseTitle)
gives the values I think you may be seeking including

Coldfish · Apr 20, 2019

jjacker said:
Hi Excel Masters,

I just need your assistance on how to answer the 'CourseAccessed' column from my table, wherein it will put 1 for each Unique 'Username' accessed(TimesAccessed) a Unique course in 'CourseTitle' column whatever Lesson(LessonNumber) it is, as long as he accessed the course.

Here is the table of my example:

Username CourseTitle LessonNumber TimesAccessed CourseAccessed
Peter Excel 101 Lesson 1
2
Peter Excel 101 Lesson 2
2
Peter Working with Charts Lesson 1
0
John Excel 101 Lesson 1
11
John Excel 101 Lesson 2
11
John Excel 101 Lesson 3
11
John Working with Charts Lesson 1
5
John Working with Charts Lesson 2
5
Fred Excel 101 Lesson 1
0
Fred Excel 101 Lesson 2
0
Fred Working with Charts Lesson 1
2
Daniel Excel 101 Lesson 1
4
Daniel Excel 101 Lesson 2
3
Daniel Excel 101 Lesson 3
2
Robert Excel 101 Lesson 1
1
Robert Working with Charts Lesson 1
4
Robert Working with Charts Lesson 2
0
Ryan Excel 101 Lesson 1
1
Ryan Excel 101 Lesson 2
0

My idea here is to create a pivot table, and sum the number of users who accessed the courses. So I will be able to see the trending course accessed by the users.
And yes, the 'CourseAccessed' is a helper column so I can do the counting.

If you have better idea, please feel free to suggest. Your help is much appreciated. .

Thanks in Advance. More powers to your site.

I'm not sure I understand the question but it would seem that you could created a pivot table using the UserName column in the row and also in the value(count).

jjacker · Apr 20, 2019

Sorry if there is a confusion, what I'm after is the output for my pivot table that will look like this:

Row Labels	Sum of CourseAccessed
Excel 101	5
Working with Charts	3
Grand Total	8

Therefore, I should use a helper column(CourseAccessed) that will put value of TRUE(1) if the Username had accessed a Unique CourseTitle regardless of LessonNumber as long as it was accessed. And the helper column should look like this:

Username	CourseTitle	LessonNumber	TimesAccessed	CourseAccessed
Peter	Excel 101	Lesson 1	2	1
Peter	Excel 101	Lesson 2	2	Value is 0 because it had been accessed already above ----> 0
Peter	Working with Charts	Lesson 1	0	0
John	Excel 101	Lesson 1	11	1
John	Excel 101	Lesson 2	11	0
John	Excel 101	Lesson 3	11	0
John	Working with Charts	Lesson 1	5	1
John	Working with Charts	Lesson 2	5	0
Fred	Excel 101	Lesson 1	0	0
Fred	Excel 101	Lesson 2	0	0
Fred	Working with Charts	Lesson 1	2	1
Daniel	Excel 101	Lesson 1	4	1
Daniel	Excel 101	Lesson 2	3	0
Daniel	Excel 101	Lesson 3	2	0
Robert	Excel 101	Lesson 1	1	1
Robert	Working with Charts	Lesson 1	4	1
Robert	Working with Charts	Lesson 2	0	0
Ryan	Excel 101	Lesson 1	1	1
Ryan	Excel 101	Lesson 2	0	0

Sadly, I only put the values Manually in the helper column

The problem is I cant do it manually on 100+ rows.

Thank you for the effort guys.

jjacker · Apr 20, 2019

Peter Bartholomew said:
Still futures but, when it is released, dynamic ranges will offer a simple clean alternative to pivot tables.
First a list of unique combinations of Username and CourseTitle is provided by
= UNIQUE( Accessed[[Username]:[CourseTitle]] )

Naming the columns of the new list, and using them as the criteria for an array of SUMIFS
= SUMIFS(Accessed[TimesAccessed], Accessed[Username], Username, Accessed[CourseTitle], CourseTitle)
gives the values I think you may be seeking including

View attachment 59625

Hi @Peter Bartholomew , is that '= UNIQUE' function will be released soon? I might get use of that in case. Thank you for the information.

btw, sorry for the confusion as what I want to count for the pivot is the number of course accessed by user regardless they took Lesson 2 or Lesson 1. and should look like this on pivot:

therefore the table for the helper column(CourseAccessed) will look like this:

Thank your for helping me with this @Peter Bartholomew I really appreciate your effort.

jjacker · Apr 20, 2019

Coldfish said:
I'm not sure I understand the question but it would seem that you could created a pivot table using the UserName column in the row and also in the value(count).

@Coldfish , sorry for the confusion, sadly what I want to get is the Unique CourseAccessed count, you might have a better idea, here's how it should look like for the pivot.

thanks

Peter Bartholomew · Apr 20, 2019

The UNIQUE function (like FILTER and SORT) will only be in Excel 365 in the foreseeable future. At present they are in beta release within the insider version. The ways of working can be very different from traditional methods.

Having said that, I worked through to a solution by FILTERING and producing UNIQUE lists of qualifying UserNames but then was able to reinterpret the solution in terms of traditional methods (I think). This included the trick of taking a reciprocal of the count and summing to get a count of unique occurrences.

= SUM( IF(
(Accessed[CourseTitle]=CourseList) * (Accessed[TimesAccessed] > 0),
1 / COUNTIFS( Accessed[Username], Accessed[Username], Accessed[CourseTitle], CourseList, Accessed[TimesAccessed], ">0" ) ) )

AlanSidman · Apr 20, 2019

crossposted: https://www.excelforum.com/excel-ge...ltiple-criteria-for-pivoting.html#post5106523

jjacker · Apr 20, 2019

Peter Bartholomew said:
The UNIQUE function (like FILTER and SORT) will only be in Excel 365 in the foreseeable future. At present they are in beta release within the insider version. The ways of working can be very different from traditional methods.

Having said that, I worked through to a solution by FILTERING and producing UNIQUE lists of qualifying UserNames but then was able to reinterpret the solution in terms of traditional methods (I think). This included the trick of taking a reciprocal of the count and summing to get a count of unique occurrences.

= SUM( IF(
(Accessed[CourseTitle]=CourseList) * (Accessed[TimesAccessed] > 0),
1 / COUNTIFS( Accessed[Username], Accessed[Username], Accessed[CourseTitle], CourseList, Accessed[TimesAccessed], ">0" ) ) )

Wow this one seems to work. I might need to dissect how I can apply this throughout my project. Lots of thanks @Peter Bartholomew. Let's see how can I apply this formula and also create a pivot as it is required to have the raw data reference left in the workbook. your the best bro!

jjacker · Apr 20, 2019

AlanSidman said:
crossposted: https://www.excelforum.com/excel-ge...ltiple-criteria-for-pivoting.html#post5106523

Hi, isn't it allowed to post a same question to a different site? Did I violate any rules for this? Sorry, i'm just seeking help as I'm getting crazy on how to solve this problem. Anyways, I saw your website http://www.cpearson.com/excel/DebuggingVBA.aspx this might also help me. Thanks for reply btw.

vletm · Apr 20, 2019

jjacker
Did You really ask 'Hi, isn't it allowed to post a same question to a different site?'
If so,
then I have to ask,
Did You read this 'Forum Rules':

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

before start to posting questions - did You?
If not - then You should read and follow those as well as everybody else.

jjacker · Apr 20, 2019

vletm said:
jjacker
Did You really ask 'Hi, isn't it allowed to post a same question to a different site?'
If so,
then I have to ask,
Did You read this 'Forum Rules':

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

before start to posting questions - did You?
If not - then You should read and follow those as well as everybody else.

you mad @vletm ? ok ill delete my post from other website.

vletm · Apr 20, 2019

jjacker
Why?
Did You read those rules?
There are clear rules: how to do that.
Rules are made for follow.

jjacker · Apr 20, 2019

vletm said:
jjacker
Why?
Did You read those rules?
There are clear rules: how to do that.
Rules are made for follow.

I might overlook that rule, apologies, cant remember all the rules that has been posted over there. Anyways, I already exert an effort to request for deletion of the post from other site. Don't be mad @vletm , I'm still a human, I made mistakes. Take note, not just in forums, also in real life. How bout you?

Never broken any rule? Don't be mad @vletm

take it easy. I took actions. I'm new here and never said I'm perfect. So, don't be mad ok?

Peter Bartholomew · Apr 20, 2019

For the record, the dynamic array solution simply involved filtering to give a list of Usernames corresponding to each course and counting
= COUNTA( UNIQUE( FILTER(Accessed[Username], (Accessed[CourseTitle]=CourseList)*(Accessed[TimesAccessed]>0) ) ) )

A pivot table is quite possible, especially if one brings the data in using Power Query to remove duplicates.

Peter Bartholomew · Apr 20, 2019

@jjacker
I wouldn't beat yourself up too much over this! It takes a little time to get used to the etiquette that permits the forums to works smoothly.
If you think that @Coldfish or I have given you sufficient to be of value, then you could just post the fact on the other forum to ensure that anyone who answers there is aware of the state of discussion here.

vletm · Apr 20, 2019

jjacker
Hint: Think twice - or even - three times
and after that press [ Post ] if You're sure.
Try to find something positive things.

Peter Bartholomew · Apr 20, 2019

@AlanSidman
I notice that you feature one of Chip Pearson's posts in your signature. Is it just that you value that particular post or are you one of the team that appears to have taken on the responsibility of maintaining Chip's site following the sad events of last year?

jjacker · Apr 20, 2019

Peter Bartholomew said:
For the record, the dynamic array solution simply involved filtering to give a list of Usernames corresponding to each course and counting
= COUNTA( UNIQUE( FILTER(Accessed[Username], (Accessed[CourseTitle]=CourseList)*(Accessed[TimesAccessed]>0) ) ) )

A pivot table is quite possible, especially if one brings the data in using Power Query to remove duplicates.

Ok, so this one is going real. I've heard about Power Query but never get my hands on it yet. So this idea that I got in my head seems needs more than a formula and pivoting. I might take a look on those suggestions, and ofcourse, new excel lessons. Thanks for all the great Ideas @Peter Bartholomew

jjacker · Apr 20, 2019

Peter Bartholomew said:
@jjacker
I wouldn't beat yourself up too much over this! It takes a little time to get used to the etiquette that permits the forums to works smoothly.
If you think that @Coldfish or I have given you sufficient to be of value, then you could just post the fact on the other forum to ensure that anyone who answers there is aware of the state of discussion here.

And it seems that is another story of rules that i'm thinking of, they are requesting to have the answers posted there but I don't know if I can ask you and other person in this forum to post their answers there. Thanks @Peter Bartholomew , your right, I don't know the right etiquette that permits the forums, i might need to get used to it first and silence myself from posting for the moment. You have my respect.

AlanSidman · Apr 20, 2019

@Peter Bartholomew

I do not maintain Chip's site. I agree about how unfortunate his loss was. I particularly like to use his site as a reference for issues as it is unique and helpful. I particularly like what he had to say about debugging and find that many new to VBA posters are unaware of the debugging practice and post without having gone through that exercise. It is also helpful if they post debugging issues in an effort to receive help.

Nebu · Apr 23, 2019

Hi:

May be this? using Power Pivot.

Thanks

bosco_yip · Apr 23, 2019

jjacker said:
@Coldfish , sorry for the confusion, sadly what I want to get is the Unique CourseAccessed count, you might have a better idea, here's how it should look like for the pivot.

View attachment 59631

thanks

Formula solution

In G2, copied down :

=SUMPRODUCT(((B$2:B$20=F2)*(D$2:D$20>0))/COUNTIFS(D$2:D$20,D$2:D$20,B$2:B$20,B$2:B$20))

Regards
Bosco

Unique values with Multiple Criteria

Member

Well-Known Member

New Member

Member

Member

Member

Well-Known Member

Attachments

Well-Known Member

Member

Member

Excel Ninja

Member

Excel Ninja

Member

Well-Known Member

Well-Known Member

Excel Ninja

Well-Known Member

Member

Member

Well-Known Member

Excel Ninja

Attachments

Excel Ninja

Attachments