Working with a (trimmed) dataset with over 25,000 rows of data.
Objective: Determine the top 100 most common route_taken for each user_type (only two user_type i.e. casual & member). Details: Over 6,980 number_unique_routes.
Explanation: Trying to figure out either by using a pivot_table (tried but really don't know how to input data correctly) or by using a function/formula how to retrieve the most common routes taken for members and casual users. Would like to first understand how to apply the formula/function to retrieve the most common routes how many times they appear ordered by Z-A (to retrieve the top 100 rows of data). Not sure how to implement the =COUNTIF function in this scenario to count recurring text strings and how I can apply it. An explanation would really wrap my mind around it, thanks in advance.
e.g. something like this maybe? (open to suggestions on better ways to structure this) *Notewith some route_takenyou might get an instance where both members and casual riders use that route but members or casual riders could be the more common user. (See frequent_rider_route columns)
I think I made my problem quite clear - if any clarification is needed, please let me know!