Extract timings and occurences.

[ToxinUK1]

I have multiple sheets of timings. I would like to create a list (ascending or descending) of the times that occur in each sheet and the amount of times that each time occurs. Which is the easiest way to achieve it?

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ToxinUK1]

Unfortunately my laptop is managed by my organisation and macros are blocked.
Does the add-in work with Libre Office or other programs that work with Excel spreadsheets?

 

[Fluff]

No, it will only work with Excel.

Can you copy/paste a few rows from a couple of sheets.

 

[ToxinUK1]

The data is laid out like this on each sheet;

0:43.028	1:10.340	0:36.865	0:36.090	0:49.481	0:59.300	1:50.666
0:35.437	0:35.877	0:34.980	0:34.870	0:47.856	0:40.951	1:18.389
0:35.381	0:35.558	0:40.705	0:34.552	0:35.502	0:34.593	0:34.899
0:35.845	1:09.899	0:39.714	0:34.208	0:34.978	0:34.353	0:34.714
0:34.886	0:36.011	0:34.730	0:35.138	0:34.574	0:34.300	0:37.519
0:34.758	0:35.251	0:35.292	0:34.635	0:34.541	0:34.243	0:38.149
0:34.614	0:35.790	0:36.774	0:34.500	0:34.262	0:34.010	1:40.117
0:34.418	0:35.713	0:35.010	0:34.239	0:34.481	0:34.206	1:09.992
0:34.750	0:41.157	0:53.264	0:39.386	0:34.215	0:34.413	0:50.725
0:37.791	0:37.339	0:35.174	0:35.949	0:34.321	0:33.936	0:34.938
0:34.713	0:35.142	0:34.740	0:35.872	0:34.102	0:34.062	0:34.733
0:34.744	0:35.655	0:34.767	0:34.341	0:34.298	0:34.121	1:33.183

 

[Fluff]

Thanks for that
If your sheets ar consecutive how about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	00:43.0	01:10.3	00:36.9	00:36.1	00:49.5	00:59.3	01:50.7
3	00:35.4	00:35.9	00:35.0	00:34.9	00:47.9	00:41.0	01:18.4
4	00:35.4	00:35.6	00:40.7	00:34.6	00:35.5	00:34.6	00:34.9
5	00:35.8	01:09.9	00:39.7	00:34.2	00:35.0	00:34.4	00:34.7
6	00:34.9	00:36.0	00:34.7	00:35.1	00:34.6	00:34.3	00:37.5
7	00:34.8	00:35.3	00:35.3	00:34.6	00:34.5	00:34.2	00:38.1
8	00:34.6	00:35.8	00:36.8	00:34.5	00:34.3	00:34.0	01:40.1
9	00:34.4	00:35.7	00:35.0	00:34.2	00:34.5	00:34.2	01:10.0
10	00:34.8	00:41.2	00:53.3	00:39.4	00:34.2	00:34.4	00:50.7
11	00:37.8	00:37.3	00:35.2	00:35.9	00:34.3	00:33.9	00:34.9
12	00:34.7	00:35.1	00:34.7	00:35.9	00:34.1	00:34.1	00:34.7
13	00:34.7	00:35.7	00:34.8	00:34.3	00:34.3	00:34.1	01:33.2
Sheet1

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	00:43.0	01:10.3	00:36.9	00:36.1	00:49.5	00:59.3	01:50.7
3	00:35.4	00:35.9	00:35.0	00:34.9	00:47.9	00:41.0	01:18.4
4	00:35.4	00:35.6	00:40.7	00:34.6	00:35.5	00:34.6	00:34.9
5	00:35.8	01:09.9	00:39.7	00:34.2	00:35.0	00:34.4	00:34.7
6	00:34.9	00:36.0	00:34.7	00:35.1	00:34.6	00:34.3	00:37.5
7	00:34.8	00:35.3	00:35.3	00:34.6	00:34.5	00:34.2	00:38.1
8	00:34.6	00:35.8	00:36.8	00:34.5	00:34.3	00:34.0	01:40.1
9	00:34.4	00:35.7	00:35.0	00:34.2	00:34.5	00:34.2	01:10.0
10	00:34.8	00:41.2	00:53.3	00:39.4	00:34.2	00:34.4	00:50.7
11	00:37.8	00:37.3	00:35.2	00:35.9	00:34.3	00:33.9	00:34.9
12	00:34.7	00:35.1	00:34.7	00:35.9	00:34.1	00:34.1	00:34.7
13	00:34.7	00:35.7	00:34.8	00:34.3	00:34.3	00:34.1	01:33.2
Sheet2

Fluff.xlsm
	A	B
1
2	0:33.936	2
3	0:34.010	2
4	0:34.062	2
5	0:34.102	2
6	0:34.121	2
7	0:34.206	2
8	0:34.208	2
9	0:34.215	2
10	0:34.239	2
11	0:34.243	2
12	0:34.262	2
13	0:34.298	2
14	0:34.300	2
15	0:34.321	2
16	0:34.341	2
17	0:34.353	2
18	0:34.413	2
19	0:34.418	2
20	0:34.481	2
21	0:34.500	2
22	0:34.541	2
23	0:34.552	2
24	0:34.574	2
25	0:34.593	2
26	0:34.614	2
27	0:34.635	2
28	0:34.713	2
29	0:34.714	2
30	0:34.730	2
31	0:34.733	2
32	0:34.740	2
33	0:34.744	2
34	0:34.750	2
35	0:34.758	2
36	0:34.767	2
37	0:34.870	2
38	0:34.886	2
39	0:34.899	2
40	0:34.938	2
41	0:34.978	2
42	0:34.980	2
43	0:35.010	2
44	0:35.138	2
45	0:35.142	2
46	0:35.174	2
47	0:35.251	2
48	0:35.292	2
49	0:35.381	2
50	0:35.437	2
51	0:35.502	2
52	0:35.558	2
53	0:35.655	2
54	0:35.713	2
55	0:35.790	2
56	0:35.845	2
57	0:35.872	2
58	0:35.877	2
59	0:35.949	2
60	0:36.011	2
61	0:36.090	2
62	0:36.774	2
63	0:36.865	2
64	0:37.339	2
65	0:37.519	2
66	0:37.791	2
67	0:38.149	2
68	0:39.386	2
69	0:39.714	2
70	0:40.705	2
71	0:40.951	2
72	0:41.157	2
73	0:43.028	2
74	0:47.856	2
75	0:49.481	2
76	0:50.725	2
77	0:53.264	2
78	0:59.300	2
79	1:09.899	2
80	1:09.992	2
81	1:10.340	2
82	1:18.389	2
83	1:33.183	2
84	1:40.117	2
85	1:50.666	2
Sheet3

Cell Formulas
Range	Formula
A2:B85	A2	=LET(a,TOCOL(VSTACK(Sheet1:Sheet2!A2:G20),1),GROUPBY(a,a,COUNT,0,0))
Dynamic array formulas.

 

[ToxinUK1]

Do I need to add another add-in as I remember from a long time ago that additional add-ins were needed to run certain macro tasks?
All I get is '#NAME?' error. Using the Function wizard, this is caused by the 'Groupby' part of the formula?

This is the actual formula that I used;

=LET(a,TOCOL(VSTACK('[Workbook1.xlsx]Sheet1':'[Workbook2.xlsx]Sheet1'!B3:P24),1),GROUPBY(a,a,COUNT,0,0)) - could that be the issue?

 

[ToxinUK1]

After a little investigation it appears that the GROUPBY function is not available to O365 users, just to users enrolled in the beta channel for Windows Excel and Mac Excel.
There is no release date for the foreseeable future for O365.

Do you have an alternative for us mere O365 users?

 

[Fluff]

Groupby is available to "normal" users, it just depends on which channel you are on.
Are all your sheets in different workbooks or are they all in the same workbook?

 

[ToxinUK1]

I must be on the non-GROUPBY channel via my organisation.
They are in different workbooks for individual storage but there is a requirement for comparison across the workbooks.