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]

Good to see the values are working now, I can't specifically answer the Michigan Ave query as I only have a snippet of your sample data.

The following will probably be close to what you need and I added avg time vs classic & docked, you can filter by route & user type;

• Filters - route then user_type
• Rows - rideable_type
• Values - rideable_type (becomes Count of rideable type) then ride_length_convert (becomes Average of ride_length_convert)

Count and order (from A-Z) number of recurring text strings_zgoldflo.xlsx
	A	B	C	D
1
2	route_taken	(All)
3	user_type	(All)
4
5	Row Labels	Count of rideable_type	Average of ride_length
6	classic_bike	44	00:16:45
7	docked_bike	5	00:18:34
8	Grand Total	49	00:16:56
9
Route Analysis

 

[zgoldflo]

Hi @RasGhul.

I might have forgotten to mention a key nugget of information regarding this specific pivot table/filter. I only want to sort the bike_type information from the top 10 routes_taken by either classic/member users. Here is the pivot table that is now filtered to display the top 10 routes_taken among casual users. I could also toggle members to see their top 10 routes.

December_2020_trimmed.12.17.21.xlsx
	A	B	C	D	E	F
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		Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	33	0:50:26
10		Michigan Ave & Washington St to Michigan Ave & Washington St	28	0:58:06
11		Millennium Park to Millennium Park	27	0:25:48
12		Chicago Ave & Sheridan Rd to Chicago Ave & Sheridan Rd	18	0:56:41
13		Halsted St & Wrightwood Ave to Broadway & Barry Ave	17	0:09:14
14		Michigan Ave & 8th St to Michigan Ave & 8th St	14	0:36:40
15		Western Ave & Walton St to Ashland Ave & Division St	13	0:07:47
16		Michigan Ave & 14th St to Calumet Ave & 18th St	13	0:06:48
17		Eastlake Ter & Rogers Ave to Paulina St & Howard St	12	0:06:13
18		Clinton St & Roosevelt Rd to Morgan St & 18th St	12	0:08:35
19		Michigan Ave & Oak St to Michigan Ave & Oak St	12	0:36:45
20		Clark St & Armitage Ave to Clark St & Armitage Ave	12	0:32:16
21		Southport Ave & Wellington Ave to Broadway & Barry Ave	12	0:07:26
22		Grand Total	260	0:31:04
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

This is the Minisheet displaying the information for the member users displaying the top 10 routes_taken.

December_2020_trimmed.12.17.21.xlsx
	B	C	D	E	F
2	Pivot Table 'Frequented Routes'
3	Most frequented routes & stations between both casual/member users
4
5	user_type	member
6				bike_type_count
7	frequent_routes	trip_count	avg_ride_length	classic_bike	electric_bike
8	Dearborn St & Erie St to Dearborn St & Erie St	122	0:22:24	35	2
9	Halsted St & Polk St to Throop St & Taylor St	52	0:06:33
10	Broadway & Waveland Ave to Broadway & Barry Ave	39	0:06:20
11	Clinton St & Washington Blvd to Dearborn St & Erie St	39	0:10:19
12	Franklin St & Monroe St to Clinton St & Lake St	38	0:04:55
13	Eastlake Ter & Rogers Ave to Greenview Ave & Jarvis Ave	37	0:08:48
14	Bissell St & Armitage Ave to Sheffield Ave & Kingsbury St	36	0:04:31
15	Wabash Ave & Grand Ave to Dearborn St & Erie St	36	0:24:09
16	Paulina St & Flournoy St to Peoria St & Jackson Blvd	36	0:09:39
17	Wabash Ave & Roosevelt Rd to Michigan Ave & 18th St	34	0:05:27
18	Clark St & Elm St to Halsted St & Clybourn Ave	34	0:06:58
19	Grand Total	503	0:12:00
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

As you can with this Minisheet the bike_type information, as well as the ride_length information, is showing because I had included it in the 'Rows' section of the pivot table.

December_2020_trimmed.12.17.21.xlsx
	B	C	D	E	F
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
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

What I want to do is extract information from that which is already filtered to the top 10 routes_taken of both members/casual users in the "Frequent Routes" pivot table and create an additional table that solely reflects the bike_type data for those top 10 routes (and could potentially be toggled to see more).

(Side Note: would it be easier to create some sort of SQL/R query to get all this information?)

To clarify: I want the table you have displayed above for the information reflecting the bike_types which should only be classic/electric the docked_bike I eliminated from the dataset. See screenshot:

Here is the source data which I converted into a table:

December_2020_trimmed.12.17.21.xlsx
	C
9	classic_bike
December_2020

Here is a 5-minute Loom video that is describing what I am trying to accomplish and walks-through the steps I took in following your directions to create the table.

 

[zgoldflo]

In fact, what I think could be done is to manually select those top ten routes as displayed in the 'Frequent Routes' table either for member/casual users. The issue is my excel is lagging like crazy and I can't seem to be able to manually choose the rides without my excel freezing up. The source data currently has about 22,000 rows of data with 380,000 cells. Is that the problem?

Also, if I would potentially be able to manually pick the top ten routes (as reflected in the 'Frequent Routes' pivot table) in my 'Bike Type' pivot table, would I be able to choose the top ten for members and then toggle to casual and choose the top ten and be able to toggle between both member/casual without having to each time manually choose the top ten routes for each.

The goal is to eventually extract the data from the pivot table and create a static table with the data that wouldn't lag as much.

Thoughts?

 

[zgoldflo]

As you can see here:

When I select route_taken to filter, the filter doesn't show up with checkboxes allowing me to select specific routes. I refreshed the table and tried to toggle Select Multiple Items but excel keeps freezing/lagging. Anyway to solve this or is my dataset too large for excel???

 

[zgoldflo]

@RasGhul Hi,

I think I found a solution to my Pivot Table problem and that was to add a slicer. (I am new to Pivot Tables and excel (advanced features) in general, and am working my way through know what I want but not knowing exactly how to get there with excel).

But this specific Pivot Table still does not fully solve for what I am trying to achieve. What I am trying to achieve is for a Pivot Table to show from among the top ten routes_taken between casual/member users what bike_type was used for that route and how many rides within that route had either a classic_bike or electric_bike.

I did find a workaround by creating two additional columns on the 'Frequent Routes' pivot table and using the =countif function to find classic/electric bikes in the trip information. However only when I expand the + which is beside the route_taken can the bike_count information appear:

December_2020_trimmed.12.17.21.xlsx
	B	C	D	E	F
2	Pivot Table 'Frequented Routes'
3	Most frequented routes & stations between both casual/member users
4
5	user_type	casual		bike_count
6
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
frequent_routes_pt

Cell Formulas
Range	Formula
E8	E8	=COUNTIF(B5:B261, "classic_bike")
F8	F8	=COUNTIF(B11:B82,"electric_bike")

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

See MiniSheet:

December_2020_trimmed.12.17.21.xlsx
	B	C	D	E	F
2	Pivot Table 'Frequented Routes'
3	Most frequented routes & stations between both casual/member users
4
5	user_type	casual		bike_count
6
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
26	00:26:18	1	0:26:18
27	classic_bike	1	0:26:18
28	00:26:19	1	0:26:19
29	classic_bike	1	0:26:19
30	00:26:21	1	0:26:21
31	classic_bike	1	0:26:21
32	00:26:29	1	0:26:29
33	classic_bike	1	0:26:29
34	00:26:41	1	0:26:41
35	classic_bike	1	0:26:41
36	00:27:08	1	0:27:08
37	classic_bike	1	0:27:08
38	00:27:34	1	0:27:34
39	classic_bike	1	0:27:34
40	00:27:35	1	0:27:35
41	classic_bike	1	0:27:35
42	00:28:24	1	0:28:24
43	classic_bike	1	0:28:24
44	00:28:34	1	0:28:34
45	classic_bike	1	0:28:34
46	00:28:35	1	0:28:35
47	classic_bike	1	0:28:35
48	00:28:47	1	0:28:47
49	classic_bike	1	0:28:47
50	00:28:59	1	0:28:59
51	classic_bike	1	0:28:59
52	00:29:04	1	0:29:04
53	classic_bike	1	0:29:04
54	00:29:11	1	0:29:11
55	classic_bike	1	0:29:11
56	00:29:20	1	0:29:20
57	classic_bike	1	0:29:20
58	00:29:29	1	0:29:29
59	classic_bike	1	0:29:29
60	00:29:35	1	0:29:35
61	classic_bike	1	0:29:35
62	00:29:40	1	0:29:40
63	classic_bike	1	0:29:40
64	00:30:14	1	0:30:14
65	electric_bike	1	0:30:14
66	00:30:31	2	0:30:31
67	classic_bike	1	0:30:31
68	electric_bike	1	0:30:31
69	00:32:16	1	0:32:16
70	classic_bike	1	0:32:16
71	00:33:13	1	0:33:13
72	classic_bike	1	0:33:13
73	00:33:32	1	0:33:32
74	classic_bike	1	0:33:32
75	00:34:01	1	0:34:01
76	classic_bike	1	0:34:01
77	00:34:59	1	0:34:59
78	classic_bike	1	0:34:59
79	00:56:07	1	0:56:07
80	classic_bike	1	0:56:07
81	01:36:17	1	1:36:17
82	classic_bike	1	1:36:17
83	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	33	0:50:26
84	Michigan Ave & Washington St to Michigan Ave & Washington St	28	0:58:06
85	Millennium Park to Millennium Park	27	0:25:48
86	Chicago Ave & Sheridan Rd to Chicago Ave & Sheridan Rd	18	0:56:41
87	Halsted St & Wrightwood Ave to Broadway & Barry Ave	17	0:09:14
88	Michigan Ave & 8th St to Michigan Ave & 8th St	14	0:36:40
89	Western Ave & Walton St to Ashland Ave & Division St	13	0:07:47
90	Michigan Ave & 14th St to Calumet Ave & 18th St	13	0:06:48
91	Eastlake Ter & Rogers Ave to Paulina St & Howard St	12	0:06:13
92	Clinton St & Roosevelt Rd to Morgan St & 18th St	12	0:08:35
93	Michigan Ave & Oak St to Michigan Ave & Oak St	12	0:36:45
94	Clark St & Armitage Ave to Clark St & Armitage Ave	12	0:32:16
95	Southport Ave & Wellington Ave to Broadway & Barry Ave	12	0:07:26
96	Grand Total	260	0:31:04
frequent_routes_pt

Cell Formulas
Range	Formula
E8	E8	=COUNTIF(B5:B261, "classic_bike")
F8	F8	=COUNTIF(B11:B82,"electric_bike")

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

One thing that is still getting me to scratch my head is with regard to the route_taken in Column B, I formatted the filter to show only Top 10. But as you can clearly see, there is more than 10 route_taken listed. For casual_user 14 rows are listed (instead of 10) and with member_user 11 rows are listed.

December_2020_trimmed.12.17.21.xlsx
	B	C	D	E	F
2	Pivot Table 'Frequented Routes'
3	Most frequented routes & stations between both casual/member users
4
5	user_type	casual		bike_count
6
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	0	0
9	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	33	0:50:26
10	Michigan Ave & Washington St to Michigan Ave & Washington St	28	0:58:06
11	Millennium Park to Millennium Park	27	0:25:48
12	Chicago Ave & Sheridan Rd to Chicago Ave & Sheridan Rd	18	0:56:41
13	Halsted St & Wrightwood Ave to Broadway & Barry Ave	17	0:09:14
14	Michigan Ave & 8th St to Michigan Ave & 8th St	14	0:36:40
15	Western Ave & Walton St to Ashland Ave & Division St	13	0:07:47
16	Michigan Ave & 14th St to Calumet Ave & 18th St	13	0:06:48
17	Eastlake Ter & Rogers Ave to Paulina St & Howard St	12	0:06:13
18	Clinton St & Roosevelt Rd to Morgan St & 18th St	12	0:08:35
19	Michigan Ave & Oak St to Michigan Ave & Oak St	12	0:36:45
20	Clark St & Armitage Ave to Clark St & Armitage Ave	12	0:32:16
21	Southport Ave & Wellington Ave to Broadway & Barry Ave	12	0:07:26
22	Grand Total	260	0:31:04
frequent_routes_pt

Cell Formulas
Range	Formula
E8	E8	=COUNTIF(B5:B261, "classic_bike")
F8	F8	=COUNTIF(B11:B82,"electric_bike")

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

Any answers for this?

 

[RasGhul]

Ok we have a few different questions here and maybe you may need to split your pivot tables to suit the answer you are looking for but yes Slicers are the best way to go for snapshot filtering of your data.

You can also link Slicers to multiple pivot tables so you can have 1 pivot only returning bike type / Count & AVG - then another pivot table with the expanded ride_id's etc that will change together at the same time. This will save you doing your Countifs table next to your pivots...

When thinking about your pivot table fields

• Filters - "What data do I want to include in this analysis?"
• Rows - "What data do I want to see displayed?"

So to simply to make sure we're only getting the top 10 routes of data 1st

Pivot table F= User_type, Rows = route_taken, Values = Count of user_type & Avg ride length here's my output which is 10 rows

Count and order (from A-Z) number of recurring text strings_zgoldflo.xlsx
	A	B	C
1
2	user_type	(All)
3
4	Row Labels	Count of user_type	Average of ride_length
5	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	12	00:18:24
6	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	6	00:12:03
7	Larrabee St & North Ave to Halsted St & Clybourn Ave	5	00:16:14
8	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	4	00:23:57
9	Michigan Ave & 18th St to Halsted St & Clybourn Ave	2	00:30:32
10	Green St & Randolph St to Halsted St & Clybourn Ave	2	00:13:36
11	Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave	2	00:15:03
12	Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave	2	00:10:57
13	Clark St & Chicago Ave to Halsted St & Clybourn Ave	2	00:09:40
14	Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave	2	00:18:21
15	Grand Total	39	00:17:05
16
Sheet1

Doubly click on the 1st value of Count in the pivot table, a new sheet will be automatically generated by excel, only containing the data related to your pivot.

You can then extract this "Filtered data" data based on my PT fields and export to a new workbook for analysis. With the benefit of this being you can work with this smaller amount of data without your larger data set being open in an active workbook.

Count and order (from A-Z) number of recurring text strings_zgoldflo.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ride_id	user_type	rideable_type	day_of_week	started_at	ended_at	ride_length	ride_length_convert	start_station_name	end_station_name	route_taken	start_station_id	end_station_id	start_coordinates	end_coordinates	distance_traveled_miles
2	E0D35323A3F37120	member	classic_bike	4	2020-12-30T14:30:00Z	2020-12-30T14:41:00Z	12:11:36 AM	12:11:36 AM	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
3	03926929025B28C4	casual	classic_bike	3	2020-12-29T10:29:00Z	2020-12-29T10:39:00Z	12:10:30 AM	12:10:30 AM	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
4	D084CF51C605C6F1	member	classic_bike	3	2020-12-29T10:28:00Z	2020-12-29T10:39:00Z	12:10:44 AM	12:10:44 AM	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
5	06C887C108F035AB	member	classic_bike	1	2020-12-27T14:02:00Z	2020-12-27T14:14:00Z	12:11:30 AM	12:11:30 AM	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
6	5C13C2A9F537FAF4	member	classic_bike	2	2020-12-14T15:43:00Z	2020-12-14T15:53:00Z	12:09:19 AM	12:09:19 AM	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
7	82B443DA84AA3BEE	member	classic_bike	2	2020-12-14T14:55:00Z	2020-12-14T15:03:00Z	12:08:14 AM	12:08:14 AM	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
8	5B9A60CCDD6A98C0	member	classic_bike	7	2020-12-12T19:25:00Z	2020-12-12T19:32:00Z	12:06:46 AM	12:06:46 AM	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
9	EB83EB7EC542A6FB	member	classic_bike	5	2020-12-10T09:43:00Z	2020-12-10T09:53:00Z	12:09:40 AM	12:09:40 AM	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
10	CAAABC0E9EA76453	member	classic_bike	1	2020-12-06T15:48:00Z	2020-12-06T15:57:00Z	12:08:57 AM	12:08:57 AM	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
11	AFE6DE4D8061D6F5	member	classic_bike	1	2020-12-06T14:54:00Z	2020-12-06T15:04:00Z	12:09:10 AM	12:09:10 AM	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
12	BCC1E5EEDBB31529	casual	classic_bike	1	2020-12-06T12:12:00Z	2020-12-06T13:15:00Z	1:02:04 AM	1:02:04 AM	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
13	9F21F589917D69CC	casual	classic_bike	1	2020-12-06T12:12:00Z	2020-12-06T13:15:00Z	1:02:14 AM	1:02:14 AM	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
Sheet6

*Clark st & Lincoln Ave has highest ride count, If I now add route_taken to the Filter and select Clark St & Lincoln and have rideable_type in Rows here is my output;

Count and order (from A-Z) number of recurring text strings_zgoldflo.xlsx
	A	B	C
1	user_type	(All)
2	route_taken	Clark St & Lincoln Ave to Halsted St & Clybourn Ave
3
4	Row Labels	Count of user_type	Average of ride_length
5	classic_bike	12	00:18:24
6	Grand Total	12	00:18:24
7
Sheet1

I hope this helps

 

[zgoldflo]

@RasGhul Thanks for that.

I actually found another workaround which was simply adjusting the order of the values how they appeared in the "ROWS" section of the Pivot Table. When placing bike_type directly after user_type I was able to quickly extract the averages per route_taken per bike_type. From there it was simply (finding a few inconsistencies adjusting and) inputting the totals. Here is the finished product for the 'Frequent Routes' pivot table.

Some columns might look funny because I added color and the gradient fill effect.

December_2020_trimmed.12.20.21.xlsx
	B	C	D	E	F	G	H	I	J	K	L
3	Pivot Table 'Frequented Routes'
4	Most frequented routes & stations between both casual/member users
5
6	user_type	casual		bike_count & avg_ride				bike_count & avg_ride
7				casual_user				member_user
8	frequent_routes	trip_count	route_ride_avg	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg
9	Michigan Ave & 18th St to Michigan Ave & 18th St	37	0:30:09	35	0:30:08	2	0:30:22	7	0:17:46	115	0:22:41
10	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	33	0:50:26	26	0:54:15	7	0:36:13	47	0:06:45	5	0:04:42
11	Michigan Ave & Washington St to Michigan Ave & Washington St	28	0:58:06	23	1:02:07	5	0:39:39	37	0:06:24	2	0:04:59
12	Millennium Park to Millennium Park	27	0:25:48	22	0:24:35	5	0:31:09	25	0:10:38	14	0:09:45
13	Chicago Ave & Sheridan Rd to Chicago Ave & Sheridan Rd	18	0:56:41	15	0:59:53	3	0:40:44	33	0:05:05	5	0:03:52
14	Halsted St & Wrightwood Ave to Broadway & Barry Ave	17	0:09:14	4	0:21:06	13	0:05:35	29	0:10:10	8	0:03:49
15	Michigan Ave & 8th St to Michigan Ave & 8th St	14	0:36:40	11	0:43:40	3	0:11:02	32	0:04:40	4	0:03:20
16	Western Ave & Walton St to Ashland Ave & Division St	13	0:07:47	13	0:07:47	0	0	9	0:11:51	27	0:28:16
17	Michigan Ave & 14th St to Calumet Ave & 18th St	13	0:06:48	8	0:07:47	5	0:05:24	33	0:09:48	3	0:08:02
18	Eastlake Ter & Rogers Ave to Paulina St & Howard St	12	0:06:13	7	0:06:40	5	0:05:36	30	0:05:32	4	0:04:45
19	Clinton St & Roosevelt Rd to Morgan St & 18th St	12	0:08:35	1	0:09:18	11	0:08:31	15	0:08:08	19	0:06:03
20	Michigan Ave & Oak St to Michigan Ave & Oak St	12	0:36:45	8	0:28:56	4	0:52:25
21	Clark St & Armitage Ave to Clark St & Armitage Ave	12	0:32:16	7	0:34:37	5	0:28:58
22	Southport Ave & Wellington Ave to Broadway & Barry Ave	12	0:07:26	2	0:07:50	10	0:07:21
23	Grand Total	260	0:31:04	182	0:30:04	78	0:22:44	297	0:08:48	206	0:09:07
frequent_routes_pt

Cell Formulas
Range	Formula
E23,G23	E23	=SUM(E9:E22)
F23,H23	F23	=AVERAGE(F9:F21)
I23,K23	I23	=SUM(I9:I19)
J23,L23	J23	=AVERAGE(J9:J19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:E7	Other Type	Color scale		NO
B9:B24	Cell Value	top 10 values	text	NO
B9:B24	Other Type	Color scale		NO

I have a final Pivot Table I am looking to create and am not sure how to approach it or implement it. I am looking to create a 'Shared Routes' table that will include the top ten most shared routes among member/casual users. I am not sure how I could begin to extract that information from the source data. Perhaps something like an SQL query would be the best way to do so.

How I envision it to look is somewhere along the lines of the following:

December_2020_trimmed.12.20.21.xlsx
	O	P	Q	R	S	T	U	V	W	X	Y
5	Pivot Table 'Shared Routes'
6	Most shared routes & stations among casual/member users
7
8
9		trip_count		bike_count & avg_ride				bike_count & avg_ride
10				member_user				casual_user
11	shared_routes	member	casual	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg
12	<route_name>	<number>	<number>	<number>	<avg>	<number>	<avg>
13	<route_name>
14	<route_name>
15	<route_name>
16	<route_name>
17	<route_name>
18
19		<total>	<total>	<total>	<total>	<total>	<total>	<total>	<total>	<total>	<total>
20		<grand total>		<grand total>
frequent_routes_pt

Any suggestions on how I could begin extracted the most shared routes among casual and member users?

 

[RasGhul]

In post #17 if they are Pivot tables right click on the 1st route e.g. Michigan Ave & 18th st, then select Filter / Choose Top 10

If your looking for a static table using formulas

Count and order (from A-Z) number of recurring text strings_zgoldflo.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Pivot Table 'Shared Routes'
2	Most shared routes & stations among casual/member users
3
4	Rows Count
5	10	trip_count		bike_count & avg_ride				bike_count & avg_ride
6				member				casual
7	shared_routes	member	casual	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg
8	Clark St & Lincoln Ave to Halsted St & Clybourn Ave	9	3	9	00:09:33	0	00:00:00	3	00:44:56	0	00:00:00
9	Orleans St & Merchandise Mart Plaza to Halsted St & Clybourn Ave	5	1	4	00:12:35	1	00:10:12	0	00:00:00	1	00:11:45
10	Larrabee St & North Ave to Halsted St & Clybourn Ave	4	1	4	00:17:01	0	00:00:00	1	00:13:05	0	00:00:00
11	Michigan Ave & Pearson St to Halsted St & Clybourn Ave	2	2	2	00:19:17	0	00:00:00	0	00:00:00	2	00:28:36
12	Greenview Ave & Fullerton Ave to Halsted St & Clybourn Ave	1	1	1	00:11:19	0	00:00:00	1	00:10:36	0	00:00:00
13	Clark St & Chicago Ave to Halsted St & Clybourn Ave	2	0	2	00:09:40	0	00:00:00	0	00:00:00	0	00:00:00
14	Wilton Ave & Belmont Ave to Halsted St & Clybourn Ave	2	0	2	00:15:03	0	00:00:00	0	00:00:00	0	00:00:00
15	Michigan Ave & 18th St to Halsted St & Clybourn Ave	2	0	2	00:30:32	0	00:00:00	0	00:00:00	0	00:00:00
16	Green St & Randolph St to Halsted St & Clybourn Ave	2	0	2	00:13:35	0	00:00:00	0	00:00:00	0	00:00:00
17	Lake Shore Dr & Ohio St to Halsted St & Clybourn Ave	2	0	2	00:18:20	0	00:00:00	0	00:00:00	0	00:00:00
18		31	8	30	02:36:57	1	00:10:12	5	01:08:37	3	00:40:21
19		39		31			02:47:09	8			01:48:58
20
Sheet1

Cell Formulas
Range	Formula
A8:A17	A8	=INDEX(UNIQUE(SORTBY(FILTER(Table1[route_taken],Table1[Frequency]>0),Table1[Frequency],-1)),SEQUENCE($A$5))
B8:C17	B8	=COUNTIFS(Table1[user_type],B$7,Table1[route_taken],$A8)
D8:D17,F8:F17	D8	=COUNTIFS(Table1[user_type],$D$6,Table1[route_taken],$A8,Table1[rideable_type],D$7)
E8:E17	E8	=IFERROR(AVERAGEIFS(Table1[ride_length_convert],Table1[route_taken],$A8,Table1[user_type],$D$6,Table1[rideable_type],$D$7),0)
G8:G17	G8	=IFERROR(AVERAGEIFS(Table1[ride_length_convert],Table1[route_taken],$A8,Table1[user_type],$D$6,Table1[rideable_type],$F$7),0)
H8:H17,J8:J17	H8	=COUNTIFS(Table1[user_type],$H$6,Table1[route_taken],$A8,Table1[rideable_type],H$7)
I8:I17	I8	=IFERROR(AVERAGEIFS(Table1[ride_length_convert],Table1[route_taken],$A8,Table1[user_type],$H$6,Table1[rideable_type],$H$7),0)
K8:K17	K8	=IFERROR(AVERAGEIFS(Table1[ride_length_convert],Table1[route_taken],$A8,Table1[user_type],$H$6,Table1[rideable_type],$J$7),0)
B18:K18	B18	=SUM(B8:B17)
B19	B19	=SUM(B18:C18)
D19,H19	D19	=SUM(D18,F18)
G19,K19	G19	=SUM(E18,G18)
Dynamic array formulas.

 

[RasGhul]

zgold pls note that I added a column called frequency to the end of my sample data with

=COUNTIFS($K$2:K2,K2)

which feeds

=INDEX(UNIQUE(SORTBY(FILTER(Table1[route_taken],Table1[Frequency]>0),Table1[Frequency],-1)),SEQUENCE($A$5))

 

[zgoldflo]

@RasGhul Oh wow. Thank you for your time and continued effort to help.

That table looks gorgeous. I'm going to need to understand those formulas to be able to implement them correctly on the current data I have.

In reference to what you mentioned about SORT BY > TOP 10. I have that already implemented on my Pivot Tables. (I mentioned previously, the odd thing is that the TOP 10 filter has added more than 10 rows for my TOP 10 - perhaps it may be because there are more than one 'trip_count' that have the same values i.e. two different route_taken that have the same number of trip_count?)

Aside from that, are you saying that if I have the TOP 10 filter already implemented on my Pivot Table and I toggle the Table to include for example 'All results' I would be seeing the results for the most shared_routes among casual and member users?

Below on my Pivot Table, I numbered both member and casual route_taken by top results (e.g. '1m' for the top route for member users and '1c' for the top route by casual users). The yellow is member top routes (which also seem to be the top shared_route as well) while the orange is casual top routes.

What I mean by TOP SHARED ROUTES are routes that have the highest number of both member and casual users ** i.e. the most balanced 1:1 ratio between the two **. The objective is to list those shared_routes from highest to lowest. (The analysis goal is to consider why those routes attract both rider types equally i.e. what about those routes make them just as appealing to member users as casual users).

I don't understand how the table below with the toggled 'All results' / 'Multiple Items' is showing for the most balanced distribution between both member/casual users?

December_2020_trimmed.12.20.21.607.V.xlsx
	B	C	D	E	F	G	H	I	J	K	L
3	Pivot Table 'Frequented Routes'
4	Most frequented routes & stations between both casual/member users
5
6	user_type	(Multiple Items)		bike_count & avg_ride				bike_count & avg_ride
7				casual_user				member_user
8	frequent_routes	trip_count	route_ride_avg	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg	classic_bike	ride_classic_avg	electric_bike	ride_electric_avg
9	Dearborn St & Erie St to Dearborn St & Erie St -- (1m)	127	0:21:58	35	0:30:08	2	0:30:22	7	0:17:46	115	0:22:41
10	Halsted St & Polk St to Throop St & Taylor St -- (2m)	52	0:06:33	26	0:54:15	7	0:36:13	47	0:06:45	5	0:04:42
11	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave -- (2c)	47	0:41:15	23	1:02:07	5	0:39:39	37	0:06:24	2	0:04:59
12	Michigan Ave & 18th St to Michigan Ave & 18th St -- (1c)	43	0:28:53	22	0:24:35	5	0:31:09	25	0:10:38	14	0:09:45
13	Broadway & Waveland Ave to Broadway & Barry Ave -- (3m)	42	0:06:18	15	0:59:53	3	0:40:44	33	0:05:05	5	0:03:52
14	Clinton St & Washington Blvd to Dearborn St & Erie St -- (4m)	39	0:10:19	4	0:21:06	13	0:05:35	29	0:10:10	8	0:03:49
15	Franklin St & Monroe St to Clinton St & Lake St -- (5m)	38	0:04:55	11	0:43:40	3	0:11:02	32	0:04:40	4	0:03:20
16	Eastlake Ter & Rogers Ave to Greenview Ave & Jarvis Ave -- (6m)	38	0:08:48	13	0:07:47	0	0	9	0:11:51	27	0:28:16
17	Wabash Ave & Roosevelt Rd to Michigan Ave & 18th St -- (10m)	37	0:05:24	8	0:07:47	5	0:05:24	33	0:09:48	3	0:08:02
18	Wabash Ave & Grand Ave to Dearborn St & Erie St -- (8m)	36	0:24:09	7	0:06:40	5	0:05:36	30	0:05:32	4	0:04:45
19	Bissell St & Armitage Ave to Sheffield Ave & Kingsbury St -- (7m)	36	0:04:31	1	0:09:18	11	0:08:31	15	0:08:08	19	0:06:03
20	Paulina St & Flournoy St to Peoria St & Jackson Blvd -- (9m)	36	0:09:39	8	0:28:56	4	0:52:25
21	Michigan Ave & Washington St to Michigan Ave & Washington St -- (3c)	36	0:46:44	7	0:34:37	5	0:28:58
22	Grand Total	607	0:17:44	2	0:07:50	10	0:07:21
23				182	0:30:04	78	0:22:44	297	0:08:48	206	0:09:07
frequent_routes_pt

Cell Formulas
Range	Formula
E23,G23	E23	=SUM(E9:E22)
F23,H23	F23	=AVERAGE(F9:F21)
I23,K23	I23	=SUM(I9:I19)
J23,L23	J23	=AVERAGE(J9:J19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:E7	Other Type	Color scale		NO
B9:B24	Cell Value	top 10 values	text	NO
B9:B24	Other Type	Color scale		NO