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]

From the sample data set that I have when I use the top 10 filter I only get 10 routes, I'm still not sure why you're getting more.

I have another idea though, I'll post later.

 

[RasGhul]

From my data set Members far out weigh casual riders so when we do Top 10 by route_taken, this will be the distribution.

If you only do count of routes, the same list of routes seems to be returned by formula or pivot paths.

I don't think Pivot tables will give you the expanded view your looking for like in my formula table.

PT Fields Rows=route_taken Values=Count of route_taken & Avg of ride_length.

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

Cell Formulas
Range	Formula
A15:A24	A15	=INDEX(UNIQUE(SORTBY(FILTER(Table1[route_taken],Table1[Frequency]>0),Table1[Frequency],-1)),SEQUENCE(10))
B15:B24	B15	=COUNTIF(Table1[route_taken],A15)
C15:C24	C15	=AVERAGEIFS(Table1[ride_length_convert],Table1[route_taken],A15)
B25	B25	=SUM(B15:B24)
C25	C25	=AVERAGE(C15:C24)
Dynamic array formulas.

 

[zgoldflo]

@RasGhul After gathering all the data needed for the 12 months of the year, i.e. 'Frequent Monthly Routes' I am looking to consolidate all this data into one table that depicts the 'Annual Top Routes' that contains all the same data points as the 'Frequent Monthly Routes' for both members and casual users, depicting the annual totals. Currently, all the monthly data (for both members and casuals) are in separate Excel Workbooks organized by month and are displayed in Pivot Tables. What I thought to do, in considering the best method in extracting the top annual routes by users, is to create a separate workbook that organizes the data by user status in two separate sheets i.e. casual user data in one sheet and member user data in another. The data in both respective sheets will be organized by month. What I need to do is simply extract the totals listed throughout the months of the year to solve for the top annual routes. Let me share a snapshot of what I began to create but not quite finished:

12_Months_Joined.xlsx
	A	B	C	D	E	F	G	H	I
1	month	route_name	route_count	avg_ride_length		classic_bike	ride_classic_avg	electric_bike	ride_electric_avg
2		Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	85	0:38:28		69	0:42:32	16	0:20:54
3		Michigan Ave & 18th St to Michigan Ave & 18th St	37	0:30:09		35	0:30:08	2	0:30:22
4	DECEMBER	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	33	0:50:26		26	0:54:15	7	0:36:13
5		Millennium Park to Millennium Park	27	0:25:48		22	0:24:35	5	0:31:09
6		Michigan Ave & Washington St to Michigan Ave & Washington St	25	0:52:46		20	0:56:03	5	0:39:39
7		Lake Shore Dr & Monroe St to Dusable Harbor	22	0:44:14		17	0:42:50	5	0:49:00
8		Michigan Ave & Lake St to Michigan Ave & Lake St	20	1:17:46		10	1:51:01	10	0:44:30
9		Dusable Harbor to Dusable Harbor	19	0:30:46		11	0:28:54	8	0:33:19
10		Shore Dr & 55th St to Shore Dr & 55th St	18	0:32:10		16	0:30:20	2	0:46:54
11		Chicago Ave & Sheridan Rd to Chicago Ave & Sheridan Rd	18	0:56:41		15	0:59:53	3	0:40:44
12		Blackstone Ave & Hyde Park Blvd to Ellis Ave & 58th St	18	0:10:10		13	0:10:57	5	0:08:07
13		Grand Total December	322	0:40:15		254	0:44:41	68	0:34:37
14
15	JANUARY	Michigan Ave & 18th St to Michigan Ave & 18th St	39	0:29:37		39	0:29:37	0	0:00:00
16		Ellis Ave & 60th St to Ellis Ave & 55th St	37	0:07:39		37	0:07:39	0	0:00:00
17		Kimbark Ave & 53rd St to Ellis Ave & 60th St	29	0:13:11		28	0:13:12	1	0:12:34
18		Ellis Ave & 55th St to Ellis Ave & 60th St	26	0:06:20		26	0:06:20	0	0:00:00
19		Shields Ave & 28th Pl to Michigan Ave & 14th St	18	0:13:17		18	0:13:17	0	0:00:00
20		Winthrop Ave & Lawrence Ave to Southport Ave & Waveland Ave	18	0:11:38		16	0:11:45	2	0:10:37
21		Broadway & Cornelia Ave to Broadway & Cornelia Ave	17	0:45:27		11	1:01:22	6	0:16:17
22		Sedgwick St & Huron St to Clark St & Elm St	17	0:07:13		16	0:07:12	1	0:07:31
23		Ellis Ave & 60th St to Kimbark Ave & 53rd St	17	0:13:30		15	0:14:16	2	0:07:46
24		Daley Center Plaza to Clark St & 9th St (AMLI)	17	0:08:43		11	0:09:29	6	0:07:18
25		Michigan Ave & Lake St to Michigan Ave & Lake St	17	0:40:40		15	0:44:09	2	0:14:35
26		Grand Total January	252	0:17:27		232	0:18:12	20	0:06:23
Casual

Cell Formulas
Range	Formula
F26,H26	F26	=SUM(F15:F25,)
G26,I26	G26	=AVERAGE(G15:G25,)

Only the months for December, January is currently there. As you can see this sheet shows only the casual user data and is listed by month. The data displayed are the monthly (Dec & Jan) totals for frequented routes. I need to find any matching route_names and tally up the totals to create my annual dataset. For example, as you can see the route 'Michigan Ave & 18th St to Michigan Ave & 18th St' is repeated between Jan & Dec. I would like to figure out a way to implement a count to tally up all matching route_names and their respective data and be able to implement this across all 12 months once listed in the sheet.

If you don't understand what it is I am trying to accomplish I have included a short Loom video explaining what I am trying to accomplish.

Would it make more sense to upload this data into RStudio or an SQL server to implement code to get totals or is this still possible with excel?

 

[zgoldflo]

Here is the finished sheet for the Casual users. I have implemented the =INDEX() formula to retrieve the most repeated text but cannot seem to get it to work correctly.

What I plan to do, is after I get my most repeated set of values i.e. route_name, I then would manually add up all the data (unless there is a better way), then set a condition to return the next most repeated value (e.g. the next value which is not the first value which was returned as the highest).

Trying to figure out how to structure this.

Above I believe I clearly outlined what I want to do. If you need further clarity I have attached another Loom video.

*Correction on my INDEX() formula. Still not working...

=INDEX(B2:B146,MODE(MATCH(B2:B146,B2:B146,0)))

Did the Ctrl+Shift+Enter as well, no luck, just returning #N/A?

Frequent-Routes-by-User.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	month	route_name	route_count	avg_ride_length		classic_bike	ride_classic_avg	electric_bike	ride_electric_avg		#N/A
2		Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	85	0:38:28		69	0:42:32	16	0:20:54
3		Michigan Ave & 18th St to Michigan Ave & 18th St	37	0:30:09		35	0:30:08	2	0:30:22
4	DECEMBER	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	33	0:50:26		26	0:54:15	7	0:36:13
5		Millennium Park to Millennium Park	27	0:25:48		22	0:24:35	5	0:31:09
6		Michigan Ave & Washington St to Michigan Ave & Washington St	25	0:52:46		20	0:56:03	5	0:39:39
7		Lake Shore Dr & Monroe St to Dusable Harbor	22	0:44:14		17	0:42:50	5	0:49:00
8		Michigan Ave & Lake St to Michigan Ave & Lake St	20	1:17:46		10	1:51:01	10	0:44:30
9		Dusable Harbor to Dusable Harbor	19	0:30:46		11	0:28:54	8	0:33:19
10		Shore Dr & 55th St to Shore Dr & 55th St	18	0:32:10		16	0:30:20	2	0:46:54
11		Chicago Ave & Sheridan Rd to Chicago Ave & Sheridan Rd	18	0:56:41		15	0:59:53	3	0:40:44
12		Blackstone Ave & Hyde Park Blvd to Ellis Ave & 58th St	18	0:10:10		13	0:10:57	5	0:08:07
13		Grand Total December	322	0:40:15		254	0:44:41	68	0:34:37
14
15	JANUARY	Michigan Ave & 18th St to Michigan Ave & 18th St	39	0:29:37		39	0:29:37	0	0:00:00
16		Ellis Ave & 60th St to Ellis Ave & 55th St	37	0:07:39		37	0:07:39	0	0:00:00
17		Kimbark Ave & 53rd St to Ellis Ave & 60th St	29	0:13:11		28	0:13:12	1	0:12:34
18		Ellis Ave & 55th St to Ellis Ave & 60th St	26	0:06:20		26	0:06:20	0	0:00:00
19		Shields Ave & 28th Pl to Michigan Ave & 14th St	18	0:13:17		18	0:13:17	0	0:00:00
20		Winthrop Ave & Lawrence Ave to Southport Ave & Waveland Ave	18	0:11:38		16	0:11:45	2	0:10:37
21		Broadway & Cornelia Ave to Broadway & Cornelia Ave	17	0:45:27		11	1:01:22	6	0:16:17
22		Sedgwick St & Huron St to Clark St & Elm St	17	0:07:13		16	0:07:12	1	0:07:31
23		Ellis Ave & 60th St to Kimbark Ave & 53rd St	17	0:13:30		15	0:14:16	2	0:07:46
24		Daley Center Plaza to Clark St & 9th St (AMLI)	17	0:08:43		11	0:09:29	6	0:07:18
25		Michigan Ave & Lake St to Michigan Ave & Lake St	17	0:40:40		15	0:44:09	2	0:14:35
26		Grand Total January	252	0:17:27		232	0:18:12	20	0:06:23
27
28	FEBRUARY	Ellis Ave & 60th St to Ellis Ave & 55th St	20	0:05:49		20	0:05:49	0	0:00
29		Daley Center Plaza to Daley Center Plaza	19	0:27:40		19	0:27:40	0	0:00
30		Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	15	0:30:39		12	0:35:44	3	0:10:17
31		Millennium Park to Millennium Park	15	0:31:49		13	0:32:11	2	0:29:22
32		Broadway & Barry Ave to Halsted St & Clybourn Ave	12	0:16:50		12	0:16:50	0	0:00
33		Wabash Ave & 16th St to Wabash Ave & 16th St	11	0:32:57		10	0:27:18	1	1:29:25
34		Michigan Ave & 8th St to Michigan Ave & 8th St	11	0:24:20		11	0:24:20	0	0:00
35		Field Blvd & South Water St to Fairbanks St & Superior St	11	0:10:23		0	0:00	11	0:10:23
36		Ellis Ave & 55th St to Ellis Ave & 60th St	11	0:05:07		10	0:05:19	1	0:03:09
37		Fairbanks St & Superior St to Field Blvd & South Water St	11	0:10:23		5	0:11:37	6	0:09:21
38		Grand Total February	136	0:19:49		112	0:18:41	24	0:15:12
39
40	MARCH	Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	292	0:37:10		253	0:38:26	39	0:29:02
41		Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	240	0:43:49		203	0:44:12	37	0:41:41
42		Millennium Park to Millennium Park	199	0:39:49		175	0:40:26	24	0:35:17
43		Michigan Ave & Oak St to Michigan Ave & Oak St	138	0:46:24		108	0:48:06	30	0:40:16
44		Indiana Ave & Roosevelt Rd to Indiana Ave & Roosevelt Rd	113	0:47:13		83	0:43:46	30	0:56:47
45		Shore Dr & 55th St to Shore Dr & 55th St	91	0:47:48		84	0:48:01	7	0:45:12
46		Buckingham Fountain to Buckingham Fountain	86	0:40:49		70	0:40:49	16	0:40:44
47		Michigan Ave & 8th St to Michigan Ave & 8th St	86	0:39:55		76	0:42:04	10	0:23:36
48		Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	83	0:33:17		66	0:32:56	17	0:34:43
49		Shedd Aquarium to Shedd Aquarium	82	0:49:55		70	0:53:49	12	0:27:10
50		Grand Total March	1410	0:41:58		1188	0:43:15	222	0:37:27
51
52	APRIL	Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	440	0:40:20		387	0:40:26	53	0:39:36
53		Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	360	0:46:01		313	0:46:41	47	0:41:33
54		Millennium Park to Millennium Park	259	0:39:47		229	0:40:03	30	0:37:42
55		Michigan Ave & Oak St to Michigan Ave & Oak St	254	0:48:52		217	0:49:27	37	0:45:28
56		Buckingham Fountain to Buckingham Fountain	198	0:52:10		170	0:52:31	28	0:50:04
57		Indiana Ave & Roosevelt Rd to Indiana Ave & Roosevelt Rd	177	0:51:07		140	0:54:07	37	0:39:45
58		Fort Dearborn Dr & 31st St to Fort Dearborn Dr & 31st St	134	0:56:18		122	0:56:45	12	0:51:43
59		Shore Dr & 55th St to Shore Dr & 55th St	125	0:43:03		106	0:40:51	19	0:55:20
60		Montrose Harbor to Montrose Harbor	124	0:47:14		98	0:47:43	26	0:45:28
61		Theater on the Lake to Theater on the Lake	121	0:44:53		113	0:43:11	8	1:08:53
62		Grand Total April	2192	0:45:54		1895	0:47:10	297	0:47:33
63
64	MAY	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	952	0:42:24		763	0:42:41	189	0:41:16
65		Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	579	0:36:59		445	0:36:51	134	0:37:26
66		Millennium Park to Millennium Park	518	0:44:32		421	0:43:24	97	0:49:27
67		Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	513	0:29:19		410	0:28:57	103	0:30:47
68		Michigan Ave & Oak St to Michigan Ave & Oak St	485	0:49:33		358	0:49:13	127	0:50:30
69		Buckingham Fountain to Buckingham Fountain	342	0:46:49		251	0:46:33	91	0:47:33
70		Indiana Ave & Roosevelt Rd to Indiana Ave & Roosevelt Rd	283	0:48:42		211	0:51:42	72	0:39:56
71		Lake Shore Dr & North Blvd to Lake Shore Dr & North Blvd	246	0:46:38		189	0:48:25	57	0:40:45
72		Streeter Dr & Grand Ave to Lake Shore Dr & Monroe St	242	0:29:14		190	0:29:12	52	0:29:25
73		Shedd Aquarium to Streeter Dr & Grand Ave	230	0:31:09		170	0:33:01	60	0:25:54
74		Grand Total May	4390	0:40:52		3408	0:41:00	982	0:39:18
75
76	JUNE	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	1181	0:42:20		926	0:42:40	255	0:41:09
77		Michigan Ave & Oak St to Michigan Ave & Oak St	618	0:50:37		483	0:52:53	135	0:42:31
78		Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	603	0:41:26		485	0:42:57	118	0:35:12
79		Millennium Park to Millennium Park	561	0:42:36		440	0:43:52	121	0:38:02
80		Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	559	0:26:54		460	0:27:39	99	0:23:24
81		Buckingham Fountain to Buckingham Fountain	543	0:43:03		356	0:40:32	187	0:47:50
82		Streeter Dr & Grand Ave to Millennium Park	418	0:30:38		345	0:31:14	73	0:27:51
83		Indiana Ave & Roosevelt Rd to Indiana Ave & Roosevelt Rd	337	0:46:58		236	0:48:31	101	0:43:20
84		Streeter Dr & Grand Ave to Michigan Ave & Oak St	336	0:23:15		282	0:22:21	54	0:27:56
85		Millennium Park to Streeter Dr & Grand Ave	329	0:38:30		285	0:38:36	44	0:37:50
86		Grand Total June	5485	0:39:41		4298	0:39:08	1187	6:05:05
87
88	JULY	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	1435	0:42:29		1212	0:42:57	223	0:39:56
89		Michigan Ave & Oak St to Michigan Ave & Oak St	685	0:46:16		549	0:47:21	136	0:41:51
90		Millennium Park to Millennium Park	557	0:40:18		461	0:40:26	96	0:39:43
91		Lake Shore Dr & Monroe St to Lake Shore Dr & Monroe St	485	0:38:31		396	0:39:05	89	0:36:01
92		Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	480	0:26:18		391	0:26:10	89	0:26:51
93		Streeter Dr & Grand Ave to Millennium Park	467	0:32:45		395	0:31:53	72	0:37:31
94		Millennium Park to Streeter Dr & Grand Ave	430	0:40:20		341	0:43:00	89	0:30:05
95		Streeter Dr & Grand Ave to Michigan Ave & Oak St	388	0:27:19		328	0:28:20	60	0:21:44
96		Shedd Aquarium to Streeter Dr & Grand Ave	347	0:37:14		277	0:36:17	70	0:40:58
97		Streeter Dr & Grand Ave to Lake Shore Dr & North Blvd	323	0:22:52		290	0:23:04	33	0:21:03
98		Grand Total July	5597	0:37:31		4640	0:35:51	957	0:33:34
99
100	AUGUST	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	1290	0:42:04		1052	0:43:06	238	0:37:28
101		Michigan Ave & Oak St to Michigan Ave & Oak St	704	0:49:04		581	0:50:29	123	0:42:25
102		DuSable Lake Shore Dr & Monroe St to DuSable Lake Shore Dr & Monroe St	607	0:41:44		475	0:42:52	132	0:37:41
103		DuSable Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	531	0:28:40		434	0:27:42	97	0:32:57
104		Millennium Park to Millennium Park	482	0:39:52		380	0:42:20	102	0:30:41
105		Streeter Dr & Grand Ave to DuSable Lake Shore Dr & North Blvd	377	0:23:29		324	0:23:40	53	0:22:20
106		Millennium Park to Streeter Dr & Grand Ave	372	0:40:30		319	0:40:48	53	0:38:42
107		Theater on the Lake to Theater on the Lake	369	0:45:12		313	0:45:35	56	0:42:59
108		Streeter Dr & Grand Ave to Millennium Park	357	0:32:47		296	0:34:05	61	0:26:27
109		Shedd Aquarium to Streeter Dr & Grand Ave	349	0:34:48		295	0:34:46	54	0:35:01
110		Grand Total August	5438	0:39:10		4469	0:38:32	969	0:34:40
111
112	SEPTEMBER	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	1001	0:42:59		831	0:43:28	170	0:40:38
113		DuSable Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	530	0:25:16		443	0:24:58	87	0:26:47
114		Michigan Ave & Oak St to Michigan Ave & Oak St	499	0:55:30		405	0:57:53	94	0:45:12
115		DuSable Lake Shore Dr & Monroe St to DuSable Lake Shore Dr & Monroe St	473	0:42:51		387	0:42:42	86	0:43:31
116		Millennium Park to Millennium Park	338	0:45:32		262	0:48:00	76	0:36:59
117		Streeter Dr & Grand Ave to Millennium Park	319	0:34:44		260	0:34:41	59	0:34:57
118		Streeter Dr & Grand Ave to DuSable Lake Shore Dr & North Blvd	298	0:23:23		270	0:23:32	28	0:21:59
119		Streeter Dr & Grand Ave to Michigan Ave & Oak St	274	0:25:39		227	0:25:32	47	0:26:09
120		Streeter Dr & Grand Ave to DuSable Lake Shore Dr & Monroe St	272	0:31:33		231	0:29:05	41	0:45:23
121		Shedd Aquarium to Streeter Dr & Grand Ave	257	0:31:00		202	0:32:12	55	0:26:36
122		Grand Total September	4261	0:37:53		3518	0:36:12	743	0:34:49
123
124	OCTOBER	Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	474	0:39:01		358	0:41:57	116	0:29:56
125		DuSable Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	305	0:26:00		244	0:25:39	61	0:27:25
126		DuSable Lake Shore Dr & Monroe St to DuSable Lake Shore Dr & Monroe St	290	0:35:41		214	0:37:47	76	0:29:45
127		Millennium Park to Millennium Park	271	0:40:48		189	0:44:52	82	0:31:28
128		Ellis Ave & 60th St to Ellis Ave & 55th St	217	0:07:17		199	0:07:34	18	0:04:17
129		Streeter Dr & Grand Ave to Millennium Park	205	0:29:51		152	0:32:06	53	0:23:23
130		Ellis Ave & 60th St to University Ave & 57th St	198	0:08:17		182	0:08:42	16	0:03:39
131		Michigan Ave & Oak St to Michigan Ave & Oak St	193	0:43:48		118	0:44:57	75	0:42:01
132		Ellis Ave & 55th St to Ellis Ave & 60th St	191	0:07:26		177	0:07:42	14	0:04:00
133		Millennium Park to Streeter Dr & Grand Ave	185	0:44:42		142	0:46:43	43	0:38:02
134		Grand Total October	2529	0:29:47		1975	0:29:48	554	0:23:23
135
136	NOVEMBER	DuSable Lake Shore Dr & Monroe St to Streeter Dr & Grand Ave	156	0:25:04		108	0:27:12	48	0:20:16
137		DuSable Lake Shore Dr & Monroe St to DuSable Lake Shore Dr & Monroe St	139	0:34:51		99	0:39:24	40	0:23:37
138		Streeter Dr & Grand Ave to Streeter Dr & Grand Ave	136	0:34:31		98	0:34:02	38	0:35:46
139		Ellis Ave & 60th St to Ellis Ave & 55th St	112	0:07:05		107	0:07:14	5	0:03:53
140		Ellis Ave & 60th St to University Ave & 57th St	111	0:10:18		106	0:10:35	5	0:04:27
141		Millennium Park to Millennium Park	99	0:29:58		52	0:28:15	47	0:31:53
142		Ellis Ave & 55th St to Ellis Ave & 60th St	94	0:07:19		89	0:07:28	5	0:04:46
143		Chicago Ave & Sheridan Rd to Sheridan Rd & Noyes St (NU)	93	0:06:21		53	0:06:47	40	0:05:47
144		Kimbark Ave & 53rd St to University Ave & 57th St	85	0:15:02		76	0:15:46	9	0:08:55
145		Streeter Dr & Grand Ave to DuSable Lake Shore Dr & Monroe St	83	0:15:13		44	0:15:53	39	0:14:28
146		Grand Total November	1108	0:20:01		832	0:19:15	276	0:15:23
Casual

Cell Formulas
Range	Formula
K1	K1	=INDEX(B2:I146,MODE(MATCH(B2:I146,B2:I146,0)))
F26,H26	F26	=SUM(F15:F25,)
G26,I26	G26	=AVERAGE(G15:G25,)
F62,H62	F62	=SUM(F52:F61)
G62,I62	G62	=AVERAGE(G52:G61)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[zgoldflo]

Actually,

I figured out how to do this using a Pivot Table. Thanks!!!