Hello All,
I have a sheet that has three columns, one for user ID, one for course name and another for completion status.
I have a problem of duplication for each course as shown below, so for one course i might get a user ID assigned twoice, one completed and one not started.
I want to capture the most up to date completion status for each user for each course on the following conditions:
user ID 1: Completed - I want to capture this only
user ID 1:Not started
User ID 1: In progress
Or if
user ID 1: Not started- I want to capture this only
user ID 1: Not started
User ID 1: In progress
so, I created a ranking system "count if that works on filtered data" for each time the user gets mentioned "while i sort the completion so completed comes first then in progress and lastly not started" in the user ID column so that when I filter the course, the user ID gets a rank like the following
Filter: Course 1
User ID Status Rank
User 1 Not started 1
User 1 Not started 2
User 1 In progress 3
Now I can get the most updated status for the course, but the issue is that the ranking is not limited to the filter as when i insert the slicer, ranking counts the users deuplication for all courses not the filterd one.
I apologize for the complication, but this is my only way around. Is there a way that i can filter the rank to mention only dependent data based on the course filter.
I have a sheet that has three columns, one for user ID, one for course name and another for completion status.
I have a problem of duplication for each course as shown below, so for one course i might get a user ID assigned twoice, one completed and one not started.
I want to capture the most up to date completion status for each user for each course on the following conditions:
user ID 1: Completed - I want to capture this only
user ID 1:Not started
User ID 1: In progress
Or if
user ID 1: Not started- I want to capture this only
user ID 1: Not started
User ID 1: In progress
so, I created a ranking system "count if that works on filtered data" for each time the user gets mentioned "while i sort the completion so completed comes first then in progress and lastly not started" in the user ID column so that when I filter the course, the user ID gets a rank like the following
Filter: Course 1
User ID Status Rank
User 1 Not started 1
User 1 Not started 2
User 1 In progress 3
Now I can get the most updated status for the course, but the issue is that the ranking is not limited to the filter as when i insert the slicer, ranking counts the users deuplication for all courses not the filterd one.
I apologize for the complication, but this is my only way around. Is there a way that i can filter the rank to mention only dependent data based on the course filter.
