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

[zgoldflo]

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 (@Joe4 any ideas?).

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)

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

See below MiniSheet

December_2020_trimmed_v2.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ride_id	user_type	rideable_type	day_of_week	started_at	ended_at	ride_length	start_station_name	end_station_name	route_taken	start_station_id	end_station_id	start_coordinates	end_coordinates	distance_traveled_miles
2	8AC7643FDA05E1D4	member	classic_bike	3	2020-12-08T05:41:00Z	2020-12-08T06:21:00Z	0:40:05	Larrabee St & North Ave	Halsted St & Clybourn Ave	Larrabee St & North Ave to Halsted St & Clybourn Ave	TA1306000008	331	41.91021, -87.6435	41.909668, -87.648128	0.24089942
3	22B721668F54DF09	member	classic_bike	4	2020-12-09T05:55:00Z	2020-12-09T06:17:00Z	0:21:56	Larrabee St & North Ave	Halsted St & Clybourn Ave	Larrabee St & North Ave to Halsted St & Clybourn Ave	TA1306000008	331	41.91021, -87.6435	41.909668, -87.648128	0.24089942
4	0AE1B9DB2F1F8BC2	member	classic_bike	1	2020-12-13T12:00:00Z	2020-12-13T12:03:00Z	0:03:03	Larrabee St & North Ave	Halsted St & Clybourn Ave	Larrabee St & North Ave to Halsted St & Clybourn Ave	TA1306000008	331	41.91021, -87.6435	41.909668, -87.648128	0.24089942
5	8DAEDE1893BCCBC8	member	classic_bike	6	2020-12-18T11:57:00Z	2020-12-18T12:00:00Z	0:03:00	Larrabee St & North Ave	Halsted St & Clybourn Ave	Larrabee St & North Ave to Halsted St & Clybourn Ave	TA1306000008	331	41.91021, -87.6435	41.909668, -87.648128	0.24089942
6	EEC33E416B277B7A	casual	classic_bike	1	2020-12-20T18:47:00Z	2020-12-20T19:00:00Z	0:13:05	Larrabee St & North Ave	Halsted St & Clybourn Ave	Larrabee St & North Ave to Halsted St & Clybourn Ave	TA1306000008	331	41.91021, -87.6435	41.909668, -87.648128	0.24089942
7	95B7ADC081755772	casual	classic_bike	4	2020-12-09T14:58:00Z	2020-12-09T15:08:00Z	0:10:36	Greenview Ave & Fullerton Ave	Halsted St & Clybourn Ave	Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave	TA1307000001	331	41.92533, -87.6658	41.909668, -87.648128	1.413006342
8	39CD27FB31626CD0	member	classic_bike	5	2020-12-10T11:18:00Z	2020-12-10T11:30:00Z	0:11:19	Greenview Ave & Fullerton Ave	Halsted St & Clybourn Ave	Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave	TA1307000001	331	41.92533, -87.6658	41.909668, -87.648128	1.413006342
9	5F82004CA928515E	member	classic_bike	6	2020-12-04T17:25:00Z	2020-12-04T17:51:00Z	0:25:40	Halsted St & 21st St	Halsted St & Clybourn Ave	Halsted St & 21st St to Halsted St & Clybourn Ave	13162	331	41.85378, -87.64665	41.909668, -87.648128	3.862298702
10	63A630ACCB226FB9	member	docked_bike	3	2020-12-01T16:47:00Z	2020-12-01T17:01:00Z	0:13:40	Clinton St & Washington Blvd	Halsted St & Clybourn Ave	Clinton St & Washington Blvd to Halsted St & Clybourn Ave	WL-012	331	41.88338, -87.64117	41.909668, -87.648128	1.851270921
11	E17C812526F9D612	member	classic_bike	4	2020-12-16T19:26:00Z	2020-12-16T19:41:00Z	0:14:22	Franklin St & Lake St	Halsted St & Clybourn Ave	Franklin St & Lake St to Halsted St & Clybourn Ave	TA1307000111	331	41.885837, -87.6355	41.909668, -87.648128	1.770041463
12	04B9C42C9716C149	member	classic_bike	6	2020-12-11T11:18:00Z	2020-12-11T11:27:00Z	0:09:12	Clark St & Chicago Ave	Halsted St & Clybourn Ave	Clark St & Chicago Ave to Halsted St & Clybourn Ave	13303	331	41.89675, -87.63089	41.909668, -87.648128	1.257971375
13	3F7080AE9A112A65	member	classic_bike	2	2020-12-21T13:48:00Z	2020-12-21T13:59:00Z	0:10:08	Clark St & Chicago Ave	Halsted St & Clybourn Ave	Clark St & Chicago Ave to Halsted St & Clybourn Ave	13303	331	41.89675, -87.63089	41.909668, -87.648128	1.257971375
14	DFF386148F369DFF	casual	docked_bike	6	2020-12-04T16:48:00Z	2020-12-04T17:17:00Z	0:28:52	Michigan Ave & Pearson St	Halsted St & Clybourn Ave	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	13034	331	41.89766, -87.62351	41.909668, -87.648128	1.513630094
15	6C6D711A027111B2	casual	docked_bike	6	2020-12-04T16:48:00Z	2020-12-04T17:17:00Z	0:28:21	Michigan Ave & Pearson St	Halsted St & Clybourn Ave	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	13034	331	41.89766, -87.62351	41.909668, -87.648128	1.513630094
16	E91724E781837DD6	member	classic_bike	1	2020-12-27T13:51:00Z	2020-12-27T14:04:00Z	0:13:44	Michigan Ave & Pearson St	Halsted St & Clybourn Ave	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	13034	331	41.89766, -87.62351	41.909668, -87.648128	1.513630094
17	F4299DE0DFDDFD36	member	classic_bike	1	2020-12-27T14:41:00Z	2020-12-27T15:05:00Z	0:24:50	Michigan Ave & Pearson St	Halsted St & Clybourn Ave	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	13034	331	41.89766, -87.62351	41.909668, -87.648128	1.513630094
18	0512A33336892105	member	classic_bike	6	2020-12-04T13:04:00Z	2020-12-04T13:14:00Z	0:09:58	Ashland Ave & Augusta Blvd	Halsted St & Clybourn Ave	Ashland Ave & Augusta Blvd to Halsted St & Clybourn Ave	13248	331	41.899643, -87.6677	41.909668, -87.648128	1.221793274
19	D3C1914BB8301A7F	member	classic_bike	1	2020-12-13T17:29:00Z	2020-12-13T17:34:00Z	0:05:04	Larrabee St & Menomonee St	Halsted St & Clybourn Ave	Larrabee St & Menomonee St to Halsted St & Clybourn Ave	TA1306000007	331	41.91468, -87.64332	41.909668, -87.648128	0.425489373
20	9F21F589917D69CC	casual	classic_bike	1	2020-12-06T12:12:00Z	2020-12-06T13:15:00Z	1:02:14	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
21	BCC1E5EEDBB31529	casual	classic_bike	1	2020-12-06T12:12:00Z	2020-12-06T13:15:00Z	1:02:04	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
22	AFE6DE4D8061D6F5	member	classic_bike	1	2020-12-06T14:54:00Z	2020-12-06T15:04:00Z	0:09:10	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
23	CAAABC0E9EA76453	member	classic_bike	1	2020-12-06T15:48:00Z	2020-12-06T15:57:00Z	0:08:57	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
24	EB83EB7EC542A6FB	member	classic_bike	5	2020-12-10T09:43:00Z	2020-12-10T09:53:00Z	0:09:40	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
25	5B9A60CCDD6A98C0	member	classic_bike	7	2020-12-12T19:25:00Z	2020-12-12T19:32:00Z	0:06:46	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
26	82B443DA84AA3BEE	member	classic_bike	2	2020-12-14T14:55:00Z	2020-12-14T15:03:00Z	0:08:14	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
27	5C13C2A9F537FAF4	member	classic_bike	2	2020-12-14T15:43:00Z	2020-12-14T15:53:00Z	0:09:19	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
28	06C887C108F035AB	member	classic_bike	1	2020-12-27T14:02:00Z	2020-12-27T14:14:00Z	0:11:30	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
29	D084CF51C605C6F1	member	classic_bike	3	2020-12-29T10:28:00Z	2020-12-29T10:39:00Z	0:10:44	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
30	03926929025B28C4	casual	classic_bike	3	2020-12-29T10:29:00Z	2020-12-29T10:39:00Z	0:10:30	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
31	E0D35323A3F37120	member	classic_bike	4	2020-12-30T14:30:00Z	2020-12-30T14:41:00Z	0:11:36	Clark St & Lincoln Ave	Halsted St & Clybourn Ave	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	13179	331	41.915689, -87.6346	41.909668, -87.648128	0.810492488
32	B31E1E6AE33A971A	casual	classic_bike	6	2020-12-04T11:04:00Z	2020-12-04T11:10:00Z	0:05:54	Bissell St & Armitage Ave	Halsted St & Clybourn Ave	Bissell St & Armitage Ave to Halsted St & Clybourn Ave	13059	331	41.91844, -87.65222	41.909668, -87.648128	0.641575778
33	29A5EF9EB208696C	member	classic_bike	7	2020-12-19T16:23:00Z	2020-12-19T16:37:00Z	0:13:53	Wilton Ave & Belmont Ave	Halsted St & Clybourn Ave	Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave	TA1307000134	331	41.94018, -87.65304	41.909668, -87.648128	2.123278511
34	24D4230E15AB999C	member	classic_bike	4	2020-12-23T17:32:00Z	2020-12-23T17:48:00Z	0:16:13	Wilton Ave & Belmont Ave	Halsted St & Clybourn Ave	Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave	TA1307000134	331	41.94018, -87.65304	41.909668, -87.648128	2.123278511
35	B487D55039F024CC	member	classic_bike	7	2020-12-05T14:14:00Z	2020-12-05T14:33:00Z	0:19:13	Halsted St & Roscoe St	Halsted St & Clybourn Ave	Halsted St & Roscoe St to Halsted St & Clybourn Ave	TA1309000025	331	41.94367, -87.64895	41.909668, -87.648128	2.34972925
36	16B7FFA064B5D6E7	member	classic_bike	5	2020-12-10T23:47:00Z	2020-12-11T00:11:00Z	0:23:15	Southport Ave & Waveland Ave	Halsted St & Clybourn Ave	Southport Ave & Waveland Ave to Halsted St & Clybourn Ave	13235	331	41.94815, -87.66394	41.909668, -87.648128	2.780353473
37	F132B0A5A3A8E618	member	classic_bike	7	2020-12-05T08:59:00Z	2020-12-05T09:28:00Z	0:28:39	Michigan Ave & 18th St	Halsted St & Clybourn Ave	Michigan Ave & 18th St to Halsted St & Clybourn Ave	13150	331	41.857813, -87.62455	41.909668, -87.648128	3.782615327
38	F8A1B3A64886E6DE	member	classic_bike	5	2020-12-17T09:09:00Z	2020-12-17T09:41:00Z	0:32:26	Michigan Ave & 18th St	Halsted St & Clybourn Ave	Michigan Ave & 18th St to Halsted St & Clybourn Ave	13150	331	41.857813, -87.62455	41.909668, -87.648128	3.782615327
39	2BB5A5176858AF4F	member	classic_bike	6	2020-12-04T15:35:00Z	2020-12-04T16:01:00Z	0:25:40	Morgan St & Polk St	Halsted St & Clybourn Ave	Morgan St & Polk St to Halsted St & Clybourn Ave	TA1307000130	331	41.871737, -87.65103	41.909668, -87.648128	2.625068488
40	F7BC88663BD0E589	member	classic_bike	7	2020-12-05T11:22:00Z	2020-12-05T11:39:00Z	0:16:59	Green St & Randolph St	Halsted St & Clybourn Ave	Green St & Randolph St to Halsted St & Clybourn Ave	13053	331	41.883668, -87.64867	41.909668, -87.648128	1.796671786
41	F2434FA3DA7E375B	member	classic_bike	2	2020-12-21T15:48:00Z	2020-12-21T15:58:00Z	0:10:12	Green St & Randolph St	Halsted St & Clybourn Ave	Green St & Randolph St to Halsted St & Clybourn Ave	13053	331	41.883668, -87.64867	41.909668, -87.648128	1.796671786
42	B9B5896F3987E87C	member	docked_bike	4	2020-12-02T19:14:00Z	2020-12-02T19:24:00Z	0:10:12	Orleans St & Merchandise Mart Plaza	Halsted St & Clybourn Ave	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	TA1305000022	331	41.888243, -87.63639	41.909668, -87.648128	1.598702019
43	48317544FE71CA3D	casual	docked_bike	5	2020-12-03T05:24:00Z	2020-12-03T05:36:00Z	0:11:45	Orleans St & Merchandise Mart Plaza	Halsted St & Clybourn Ave	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	TA1305000022	331	41.888243, -87.63639	41.909668, -87.648128	1.598702019
44	9E880C15C20F4C0E	member	classic_bike	7	2020-12-05T07:47:00Z	2020-12-05T08:03:00Z	0:15:11	Orleans St & Merchandise Mart Plaza	Halsted St & Clybourn Ave	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	TA1305000022	331	41.888243, -87.63639	41.909668, -87.648128	1.598702019
45	3946CF247E7412D8	member	classic_bike	1	2020-12-06T09:43:00Z	2020-12-06T09:57:00Z	0:14:09	Orleans St & Merchandise Mart Plaza	Halsted St & Clybourn Ave	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	TA1305000022	331	41.888243, -87.63639	41.909668, -87.648128	1.598702019
46	5762DE06DBBF5E56	member	classic_bike	5	2020-12-17T17:24:00Z	2020-12-17T17:36:00Z	0:11:48	Orleans St & Merchandise Mart Plaza	Halsted St & Clybourn Ave	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	TA1305000022	331	41.888243, -87.63639	41.909668, -87.648128	1.598702019
47	480DD48DCE8F8F07	member	classic_bike	2	2020-12-28T15:13:00Z	2020-12-28T15:22:00Z	0:09:13	Orleans St & Merchandise Mart Plaza	Halsted St & Clybourn Ave	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	TA1305000022	331	41.888243, -87.63639	41.909668, -87.648128	1.598702019
48	B044F542D7E52FF5	member	classic_bike	4	2020-12-23T13:18:00Z	2020-12-23T13:37:00Z	0:19:16	Lake Shore Dr & Ohio St	Halsted St & Clybourn Ave	Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave	TA1306000029	331	41.89257, -87.614492	41.909668, -87.648128	2.094717503
49	D000F7DF98EB762C	member	classic_bike	5	2020-12-31T17:21:00Z	2020-12-31T17:38:00Z	0:17:25	Lake Shore Dr & Ohio St	Halsted St & Clybourn Ave	Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave	TA1306000029	331	41.89257, -87.614492	41.909668, -87.648128	2.094717503
50	3BDEE3EFF60A7715	casual	classic_bike	2	2020-12-21T12:24:00Z	2020-12-21T12:44:00Z	0:20:29	Damen Ave & Chicago Ave	Halsted St & Clybourn Ave	Damen Ave & Chicago Ave to Halsted St & Clybourn Ave	13132	331	41.895769, -87.67722	41.909668, -87.648128	1.777778451
December_2020_trimmed_v2

Cell Formulas
Range	Formula
J2:J50	J2	=CONCAT(H2," to ", I2)

 

[RasGhul]

Hi zgold,

Maybe this suits, probably the easiest to deal with considering your data size?

Using a Pivot Table with these Pivot Table fields

• Filters = user_type (this allows to switch between All / member / casual with drop down or Slicer)
• Rows = route_taken
• Values = Drag user-type, this will become "Count of user_type"
• Right Click on the 1st route in the Pivot Table, Choose Filter / Top 10 / in the "Show criteria" enter 100 for the Top 100 routes
• Right Click the 1st count value in the pivot table, Sort Largest to Smallest (which is Z-A)

Book1
	A	B
1	user_type	member
2
3	route_taken	Count of user_type
4	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	9
5	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	5
6	Larrabee St & North Ave to Halsted St & Clybourn Ave	4
7	Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave	2
8	Clark St & Chicago Ave to Halsted St & Clybourn Ave	2
9	Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave	2
10	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	2
11	Michigan Ave & 18th St to Halsted St & Clybourn Ave	2
12	Green St & Randolph St to Halsted St & Clybourn Ave	2
13	Clinton St & Washington Blvd to Halsted St & Clybourn Ave	1
14	Franklin St & Lake St to Halsted St & Clybourn Ave	1
15	Larrabee St & Menomonee St to Halsted St & Clybourn Ave	1
16	Morgan St & Polk St to Halsted St & Clybourn Ave	1
17	Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave	1
18	Southport Ave & Waveland Ave to Halsted St & Clybourn Ave	1
19	Halsted St & 21st St to Halsted St & Clybourn Ave	1
20	Ashland Ave & Augusta Blvd to Halsted St & Clybourn Ave	1
21	Halsted St & Roscoe St to Halsted St & Clybourn Ave	1
Sheet2

 

[Fluff]

Another option with formulae. The Qty in Q2 is the max number of rows returned.

+Fluff 1.xlsm
	A	B								J						P	Q	R	S	T	U
1	ride_id	user_type								route_taken							Qty	Route	Total	Member	Casual
2	8AC7643FDA05E1D4	member								Larrabee St & North Ave to Halsted St & Clybourn Ave							10	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	12	9	3
3	22B721668F54DF09	member								Larrabee St & North Ave to Halsted St & Clybourn Ave								Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	6	5	1
4	0AE1B9DB2F1F8BC2	member								Larrabee St & North Ave to Halsted St & Clybourn Ave								Larrabee St & North Ave to Halsted St & Clybourn Ave	5	4	1
5	8DAEDE1893BCCBC8	member								Larrabee St & North Ave to Halsted St & Clybourn Ave								Michigan Ave & Pearson St to Halsted St & Clybourn Ave	4	2	2
6	EEC33E416B277B7A	casual								Larrabee St & North Ave to Halsted St & Clybourn Ave								Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave	2	1	1
7	95B7ADC081755772	casual								Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave								Clark St & Chicago Ave to Halsted St & Clybourn Ave	2	2	0
8	39CD27FB31626CD0	member								Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave								Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave	2	2	0
9	5F82004CA928515E	member								Halsted St & 21st St to Halsted St & Clybourn Ave								Michigan Ave & 18th St to Halsted St & Clybourn Ave	2	2	0
10	63A630ACCB226FB9	member								Clinton St & Washington Blvd to Halsted St & Clybourn Ave								Green St & Randolph St to Halsted St & Clybourn Ave	2	2	0
11	E17C812526F9D612	member								Franklin St & Lake St to Halsted St & Clybourn Ave								Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave	2	2	0
12	04B9C42C9716C149	member								Clark St & Chicago Ave to Halsted St & Clybourn Ave
13	3F7080AE9A112A65	member								Clark St & Chicago Ave to Halsted St & Clybourn Ave
14	DFF386148F369DFF	casual								Michigan Ave & Pearson St to Halsted St & Clybourn Ave
15	6C6D711A027111B2	casual								Michigan Ave & Pearson St to Halsted St & Clybourn Ave
16	E91724E781837DD6	member								Michigan Ave & Pearson St to Halsted St & Clybourn Ave
17	F4299DE0DFDDFD36	member								Michigan Ave & Pearson St to Halsted St & Clybourn Ave
18	0512A33336892105	member								Ashland Ave & Augusta Blvd to Halsted St & Clybourn Ave
19	D3C1914BB8301A7F	member								Larrabee St & Menomonee St to Halsted St & Clybourn Ave
20	9F21F589917D69CC	casual								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
21	BCC1E5EEDBB31529	casual								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
22	AFE6DE4D8061D6F5	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
23	CAAABC0E9EA76453	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
24	EB83EB7EC542A6FB	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
25	5B9A60CCDD6A98C0	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
26	82B443DA84AA3BEE	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
27	5C13C2A9F537FAF4	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
28	06C887C108F035AB	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
29	D084CF51C605C6F1	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
30	03926929025B28C4	casual								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
31	E0D35323A3F37120	member								Clark St & Lincoln Ave to Halsted St & Clybourn Ave
32	B31E1E6AE33A971A	casual								Bissell St & Armitage Ave to Halsted St & Clybourn Ave
33	29A5EF9EB208696C	member								Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave
34	24D4230E15AB999C	member								Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave
35	B487D55039F024CC	member								Halsted St & Roscoe St to Halsted St & Clybourn Ave
36	16B7FFA064B5D6E7	member								Southport Ave & Waveland Ave to Halsted St & Clybourn Ave
37	F132B0A5A3A8E618	member								Michigan Ave & 18th St to Halsted St & Clybourn Ave
38	F8A1B3A64886E6DE	member								Michigan Ave & 18th St to Halsted St & Clybourn Ave
39	2BB5A5176858AF4F	member								Morgan St & Polk St to Halsted St & Clybourn Ave
40	F7BC88663BD0E589	member								Green St & Randolph St to Halsted St & Clybourn Ave
41	F2434FA3DA7E375B	member								Green St & Randolph St to Halsted St & Clybourn Ave
42	B9B5896F3987E87C	member								Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave
43	48317544FE71CA3D	casual								Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave
44	9E880C15C20F4C0E	member								Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave
45	3946CF247E7412D8	member								Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave
46	5762DE06DBBF5E56	member								Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave
47	480DD48DCE8F8F07	member								Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave
48	B044F542D7E52FF5	member								Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave
49	D000F7DF98EB762C	member								Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave
50	3BDEE3EFF60A7715	casual								Damen Ave & Chicago Ave to Halsted St & Clybourn Ave
Lists

Cell Formulas
Range	Formula
R2:U11	R2	=LET(u,UNIQUE(FILTER(J2:J500,J2:J500<>"")),INDEX(SORT(CHOOSE({1,2,3,4},u,COUNTIFS(J2:J500,u),COUNTIFS(B2:B500,"Member",J2:J500,u),COUNTIFS(B2:B500,"casual",J2:J500,u)),2,-1),SEQUENCE(MIN(Q2,ROWS(u))),{1,2,3,4}))
J2:J50	J2	=CONCAT(H2," to ", I2)
Dynamic array formulas.

 

[zgoldflo]

@Fluff @RasGhul Thank you for your help!

[Part 1] I think the pivot table that @RasGhul showed is what would work best. However, I am also looking for a way to include (the route_id and more importantly) the avg_time (i. e. average ride_length) for all trips taken for the specific frequented route (can this be automated with the pivot table or does it have to be done manually?). Currently, the frequent_routes pivot table only includes the amount of time (ride_length) per each individual ride situated beneath the route_taken row (if ride_length included in column section of pivot table) and that is displayed vertically together with the route_id. See MiniSheet

December_2020_trimmed.12.16.21.xlsx
	A	B	C	D	E
1
2		Pivot Table 'Frequented Routes'
3		Most frequented routes & stations between both casual/member users
4
5		user_type	member
6
7		frequent_routes	number_of_trips	avg_time	route_id
8		Dearborn St & Erie St to Dearborn St & Erie St	122	<time>	<id>
9		00:01:08	1
10		13045 - 13045	1
11		00:02:57	1
12		13045 - 13045	1
13		00:03:59	1
14		13045 - 13045	1
15		00:04:56	1
16		13045 - 13045	1
17		00:05:32	1
18		13045 - 13045	1
19		00:05:58	1
20		13045 - 13045	1
frequent_routes_pt

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:B40	Cell Value	top 10 values	text	NO
B8:B40	Other Type	Color scale		NO

[Part 2] Secondly, I think I did see @Fluff touch upon this in his formula slightly (which was hard for me to follow anyway), however, I am looking to figure out a way that I could display the top shared_routes among both casual/member users. In other words, I would like the results to show the routes where the ratio of between both member/casual users are the highest i.e. which routes are the most common (either top 10-50) between them both and how those numbers are distributed between them both (e.g. the route ABC street to XYZ st is highest frequented route between both member/casual users and is distributed as 20 trips for member 14 for casual with a route_id of AZXC123 and average trip time (avg_time) of 00:25:23))).

See embedded MiniSheet

December_2020_trimmed.12.16.21.xlsx
	G	H	I	J	K	L
1
2	Pivot Table 'Shared Routes'
3	Most shared routes & stations among casual/member users
4
5
6		number_of_trips		avg_time
7	shared_routes	member	casual	member	casual	route_id
8	<name>	<number>	<number>	<time>	<time>	<id>
9	<name>
10	<name>
11	<name>
12	<name>
13	<name>
14
15		<total>	<total>	<total>	<total>
16		<grand total>		<grand total>
frequent_routes_pt

NOTE: The pivot_tables are on a separate sheet from the main data source which is here:

December_2020_trimmed.12.16.21.xlsx
	B
15	member
December_2020

[Part 3] With regard to getting the average time for ride_length, for some reason whenever I type in the =average() formula and try to get an average from my ride_length column I get an error.

December_2020_trimmed.12.16.21.xlsx
	G	H
1	ride_length
2	00:40:05	#DIV/0!
3	00:21:56	#DIV/0!
4	00:03:03
5	00:03:00
6	00:13:05
7	00:10:36
December_2020

Cell Formulas
Range	Formula
H2	H2	=AVERAGE(G2:G6)
H3	H3	=AVERAGE(G3:G8)

I assume that my data type on the ride_length column is messing up the formula, but I keep on trying to change it to hh:mm:ss format but that doesn't seem to help. Another odd thing I experienced is that when I double click the cell it keeps on reverting back to date format. See sheet

December_2020_trimmed.12.16.21.xlsx
	G	H
2	40:05.0	40:05.0
3	00:21:56	#DIV/0!
4	00:03:03
5	00:03:00
6	00:13:05
December_2020

Cell Formulas
Range	Formula
H2	H2	=AVERAGE(G2:G6)
H3	H3	=AVERAGE(G3:G8)

I want the data type to always stay in 'stopwatch' format (for lack of a better term i.e. hh:mm:ss). This issue is also bleeding over into my pivot tables. Any solutions would be greatly appreciated!

 

[RasGhul]

Hi Zgold,

Here's the pivot table output if you

• Drag ride_length under Count of user_type in the Values PT field
• This defaults to Sum of ride_length - right click top value, Summarize values by Average
• Right Click top value - Number Format / Custom / hh:mm:ss
• Drag ride_id under route_taken in the PT Rows field
• You can still swap between All / member / casual

Total Average per route with Average time per ride_id

Count and order (from A-Z) number of recurring text strings_zgoldflo.xlsx
	A	B	C
1	user_type	(All)
2
3	Row Labels	Count of user_type	Average of ride_length
4	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	12	0:18:24
5	03926929025B28C4	1	0:10:30
6	06C887C108F035AB	1	0:11:30
7	5B9A60CCDD6A98C0	1	0:06:46
8	5C13C2A9F537FAF4	1	0:09:19
9	82B443DA84AA3BEE	1	0:08:14
10	9F21F589917D69CC	1	1:02:14
11	AFE6DE4D8061D6F5	1	0:09:10
12	BCC1E5EEDBB31529	1	1:02:04
13	CAAABC0E9EA76453	1	0:08:57
14	D084CF51C605C6F1	1	0:10:44
15	E0D35323A3F37120	1	0:11:36
16	EB83EB7EC542A6FB	1	0:09:40
17	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	6	0:12:03
18	3946CF247E7412D8	1	0:14:09
19	480DD48DCE8F8F07	1	0:09:13
20	48317544FE71CA3D	1	0:11:45
21	5762DE06DBBF5E56	1	0:11:48
22	9E880C15C20F4C0E	1	0:15:11
23	B9B5896F3987E87C	1	0:10:12
24	Larrabee St & North Ave to Halsted St & Clybourn Ave	5	0:16:14
25	0AE1B9DB2F1F8BC2	1	0:03:03
26	22B721668F54DF09	1	0:21:56
27	8AC7643FDA05E1D4	1	0:40:05
28	8DAEDE1893BCCBC8	1	0:03:00
29	EEC33E416B277B7A	1	0:13:05
30	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	4	0:23:57
31	6C6D711A027111B2	1	0:28:21
32	DFF386148F369DFF	1	0:28:52
33	E91724E781837DD6	1	0:13:44
34	F4299DE0DFDDFD36	1	0:24:50
35	Clark St & Chicago Ave to Halsted St & Clybourn Ave	2	0:09:40
36	04B9C42C9716C149	1	0:09:12
37	3F7080AE9A112A65	1	0:10:08
Sheet2

 

[zgoldflo]

@RasGhul Thanks!

I tried to do that but I am getting an error. Here is a short Loom video documenting my error. I will also include a Minisheet with the current error state:

December_2020_trimmed.12.16.21.xlsx
	D
8	#DIV/0!
frequent_routes_pt

Step-by-step what I did.

1. Took ride_length and dragged it into VALUES field of pivot table under user_type.
2. Another column appeared alongside count of user_type but had the same exact values as the count of user_type column.
3. I then right-clicked to Summarize values by > Average and I received an error #DIV/0!
4. I then right-clicked Number Format>Custom>h:mm:ss

Is the data type for ride_length incorrect?

What might be the issue here? I also refreshed my Pivot Table.

 

[RasGhul]

If ride_length defaulted to count & not sum this normally means it has detected text values.

from your source data "December 2020...", when I highlight the first 3 rows of ride_length, on the bottom of the excel window it shows Average 0.015 Count 3 Sum: 0.045185185
this sum figure confirms for me that this data is actual number values, if you only see a Count value - excel only sees this data as text.
I copied your trimmed data from your original post so I suggest the following - pivot table sum then average...

Maybe try - re-drag ride_length as per my instructions to below Count of user_type, the click the down arrow for the ride_length field and select value field settings / sum
Hopefully now the pivot table has calculated the sum of ride_length, now you should be able to choose summarize values as / average.

 

[zgoldflo]

Hi @RasGhul ,

Yes, I did try this a number of times. I even recreated the Pivot Table and ensured the source data was correctly formatted - I have attached another Loom video to share with you the error I am experiencing. I am thinking maybe parsing the ride_length data and then concatenate the data afterward? Saw this thread at answers.microsoft. Just a bit at ends wits here. Thanks for sticking it out with me though.

 

[RasGhul]

Ok lets test

Can you insert a column in your source data after ride_length Column G
This might now be called Column1 as your source data should now be an excel table
in Cell H2 enter

=istext([@[ride_length]])

for all of this helper column 1 I get false, which confirms these are numeric values to calculate sum or average from
if your column returns TRUE then we can convert this to values

• in our new helper column clear the istext test so we have a fresh column
• Call this ride_length_convert
• in H2 enter

  =VALUE(LEFT([@[ride_length]],11))

• format as hh:mm:ss
• now we can use "ride_length_convert" in the pivot table instead

For curiousity sake my regional settings are, (I want to avoid you changing these yet until we exhaust excel problems 1st. )

Short date d/MM/yyyy
Long Date dddd, d MMMM yyyy
Short Time h:mm:ss tt

 

[zgoldflo]

Woohoo!

You deserve a medal! After a couple of error messages, I think we pulled through.

I'd like to show you the following.

December_2020_trimmed.12.16.21.xlsx
	A	B	C	D	E	F
1
2		Pivot Table 'Frequented Routes'
3		Most frequented routes & stations between both casual/member users
4
5		user_type	casual
6					bike_type_count
7		frequent_routes	trip_count	avg_ride_length	classic_bike	electric_bike
8		Michigan Ave & 18th St to Michigan Ave & 18th St	37	0:30:09	35	2
9		13150 - 13150	37	0:30:09
10		00:01:01	1	0:01:01
11		classic_bike	1	0:01:01
12		00:16:49	1	0:16:49
13		classic_bike	1	0:16:49
14		00:24:26	1	0:24:26
15		classic_bike	1	0:24:26
16		00:24:33	1	0:24:33
17		classic_bike	1	0:24:33
18		00:25:06	1	0:25:06
19		classic_bike	1	0:25:06
20		00:25:41	1	0:25:41
21		classic_bike	1	0:25:41
22		00:25:50	1	0:25:50
23		classic_bike	1	0:25:50
24		00:26:10	1	0:26:10
25		classic_bike	1	0:26:10
frequent_routes_pt

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:B40	Cell Value	top 10 values	text	NO
B8:B40	Other Type	Color scale		NO

If I haven't already worn out my graces with the great and mighty @RasGhul might I ask - is there any way to extract from each route the number and variation of bike_type used (i.e. classic bike vs electric bike) e.g. for the route Michigan Ave & 18th St to Michigan Ave & 18th St : 13150 - 13150 (route_id) there were 35 classic bikes used and only 2 electric. Is there a way I can populate that data for all top ten trips or would it have to be manually counted and parsed?