Count and order (Z-A) number of recurring text strings

Occasional Contributor

Hi folks.

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)
*Note 
with some route_taken you 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)

 

2.1.png

 


I think I made my problem quite clear - if any clarification is needed, please let me know!

 

2.2.JPG

 

 

1 Reply

Hi @zgoldflo 

 

here are two options:

Option 1. Pivot table:

Use the route_taken in the rows area, the user_type in the columns area and again the route_taken in the values area of the Pivot table. This will automatically apply a count.

 

DTE_1-1639555954028.png

To sort the Pivot table, open the filter icon for route_taken and select "More Sort Options..."

DTE_2-1639556013601.png

Then choose "Descending" and select "Count of route_taken" from the dropdown list

DTE_3-1639556111585.png

In addition/alternatively you can apply a value filter to get only the top X values:

DTE_4-1639556184496.png

DTE_5-1639556220232.png

 

 

Option 2. Formula solution with COUNTIFS

DTE_6-1639556276088.png

Then do a manual sort on the Grand Total column.

 

I would recommend the Pivot table, because it's much more flexible