• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique values with Multiple Criteria

J

jjacker

Member
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:

UsernameCourseTitleLessonNumberTimesAccessedCourseAccessed
PeterExcel 101Lesson 1
2​
PeterExcel 101Lesson 2
2​
PeterWorking with ChartsLesson 1
0​
JohnExcel 101Lesson 1
11​
JohnExcel 101Lesson 2
11​
JohnExcel 101Lesson 3
11​
JohnWorking with ChartsLesson 1
5​
JohnWorking with ChartsLesson 2
5​
FredExcel 101Lesson 1
0​
FredExcel 101Lesson 2
0​
FredWorking with ChartsLesson 1
2​
DanielExcel 101Lesson 1
4​
DanielExcel 101Lesson 2
3​
DanielExcel 101Lesson 3
2​
RobertExcel 101Lesson 1
1​
RobertWorking with ChartsLesson 1
4​
RobertWorking with ChartsLesson 2
0​
RyanExcel 101Lesson 1
1​
RyanExcel 101Lesson 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.
 
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

59625
 
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:

UsernameCourseTitleLessonNumberTimesAccessedCourseAccessed
PeterExcel 101Lesson 1
2​
PeterExcel 101Lesson 2
2​
PeterWorking with ChartsLesson 1
0​
JohnExcel 101Lesson 1
11​
JohnExcel 101Lesson 2
11​
JohnExcel 101Lesson 3
11​
JohnWorking with ChartsLesson 1
5​
JohnWorking with ChartsLesson 2
5​
FredExcel 101Lesson 1
0​
FredExcel 101Lesson 2
0​
FredWorking with ChartsLesson 1
2​
DanielExcel 101Lesson 1
4​
DanielExcel 101Lesson 2
3​
DanielExcel 101Lesson 3
2​
RobertExcel 101Lesson 1
1​
RobertWorking with ChartsLesson 1
4​
RobertWorking with ChartsLesson 2
0​
RyanExcel 101Lesson 1
1​
RyanExcel 101Lesson 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.
Click to expand...
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).
 
Sorry if there is a confusion, what I'm after is the output for my pivot table that will look like this:

Row LabelsSum 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:

UsernameCourseTitleLessonNumberTimesAccessedCourseAccessed
PeterExcel 101Lesson 1
2​
1​
PeterExcel 101Lesson 2
2​
Value is 0 because it had been accessed already above ----> 0​
PeterWorking with ChartsLesson 1
0​
0​
JohnExcel 101Lesson 1
11​
1​
JohnExcel 101Lesson 2
11​
0​
JohnExcel 101Lesson 3
11​
0​
JohnWorking with ChartsLesson 1
5​
1​
JohnWorking with ChartsLesson 2
5​
0​
FredExcel 101Lesson 1
0​
0​
FredExcel 101Lesson 2
0​
0​
FredWorking with ChartsLesson 1
2​
1​
DanielExcel 101Lesson 1
4​
1​
DanielExcel 101Lesson 2
3​
0​
DanielExcel 101Lesson 3
2​
0​
RobertExcel 101Lesson 1
1​
1​
RobertWorking with ChartsLesson 1
4​
1​
RobertWorking with ChartsLesson 2
0​
0​
RyanExcel 101Lesson 1
1​
1​
RyanExcel 101Lesson 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.
 
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
Click to expand...

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:

59627

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

59630

Thank your for helping me with this @Peter Bartholomew I really appreciate your effort.
 
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).
Click to expand...

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

59631

thanks
 
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" ) ) )
 

Attachments

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" ) ) )
Click to expand...

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
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.
 
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.
Click to expand...

you mad @vletm ? ok ill delete my post from other website. :)
 
vletm said:
jjacker
Why?
Did You read those rules?
There are clear rules: how to do that.
Rules are made for follow.
Click to expand...

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?
 
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.
 
@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.
 
@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?
 
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.
Click to expand...

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 :)
 
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.
Click to expand...

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. :)
 
@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.
 
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
Click to expand...
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
 

Attachments

You must log in or register to reply here.
Back
Top