Consecutive points scored basketball

[SubZero55]

Hi,

I was kindly given a solution to this problem but in trying to apply it to a larger dataset am getting some errors.

So I am seeking the most consecutive points scored by each team in Basketball games. The solution for game 1 should be 9 for Away (from 60-95 to 9-95) and 11 for Home (49-69 to 49-80)

Thanks if anyone is able to help me here.

Cheers

Book1
	A	B	C
11	Game ID	Away Score	Home Score
12	1	0	0
13	1	0	0
14	1	0	0
15	1	0	0
16	1	1	0
17	1	2	0
18	1	2	2
19	1	4	2
20	1	4	5
21	1	4	5
22	1	4	5
23	1	4	7
24	1	4	7
25	1	4	7
26	1	4	10
27	1	4	10
28	1	4	10
29	1	4	10
30	1	4	10
31	1	6	10
32	1	6	10
33	1	6	10
34	1	6	10
35	1	6	10
36	1	6	10
37	1	6	10
38	1	6	10
39	1	6	10
40	1	6	12
41	1	6	12
42	1	6	13
43	1	6	13
44	1	7	13
45	1	8	13
46	1	8	13
47	1	8	13
48	1	8	13
49	1	8	13
50	1	8	16
51	1	8	16
52	1	8	16
53	1	8	16
54	1	8	16
55	1	8	16
56	1	8	16
57	1	8	18
58	1	8	18
59	1	8	18
60	1	8	18
61	1	8	18
62	1	8	20
63	1	8	20
64	1	8	20
65	1	8	20
66	1	8	20
67	1	8	20
68	1	9	20
69	1	9	23
70	1	9	23
71	1	9	23
72	1	9	23
73	1	9	23
74	1	9	23
75	1	9	23
76	1	9	23
77	1	9	23
78	1	9	23
79	1	9	23
80	1	11	23
81	1	11	26
82	1	13	26
83	1	13	28
84	1	13	28
85	1	13	28
86	1	13	28
87	1	13	28
88	1	13	28
89	1	13	28
90	1	13	28
91	1	13	28
92	1	13	28
93	1	13	28
94	1	13	28
95	1	13	28
96	1	15	28
97	1	15	28
98	1	17	28
99	1	17	28
100	1	17	28
101	1	17	28
102	1	17	28
103	1	17	28
104	1	17	28
105	1	17	28
106	1	17	29
107	1	17	30
108	1	17	30
109	1	17	33
110	1	17	33
111	1	17	36
112	1	17	36
113	1	17	36
114	1	17	36
115	1	17	36
116	1	17	36
117	1	17	36
118	1	17	36
119	1	17	36
120	1	17	36
121	1	17	36
122	1	17	36
123	1	17	36
124	1	17	36
125	1	17	36
126	1	17	36
127	1	17	37
128	1	17	38
129	1	19	38
130	1	19	40
131	1	22	40
132	1	22	40
133	1	22	40
134	1	25	40
135	1	25	40
136	1	25	40
137	1	25	40
138	1	25	40
139	1	25	40
140	1	25	40
141	1	25	40
142	1	25	40
143	1	25	42
144	1	25	42
145	1	25	42
146	1	25	42
147	1	25	42
148	1	25	42
149	1	25	44
150	1	25	44
151	1	25	44
152	1	25	44
153	1	26	44
154	1	26	44
155	1	26	44
156	1	28	44
157	1	28	44
158	1	28	44
159	1	28	44
160	1	28	44
161	1	28	44
162	1	28	44
163	1	28	44
164	1	30	44
165	1	30	44
166	1	30	44
167	1	30	44
168	1	30	44
169	1	30	47
170	1	30	47
171	1	30	47
172	1	30	47
173	1	30	47
174	1	30	47
175	1	30	48
176	1	30	48
177	1	30	48
178	1	30	48
179	1	30	49
180	1	30	49
181	1	30	49
182	1	30	50
183	1	30	50
184	1	30	50
185	1	30	50
186	1	30	50
187	1	30	50
188	1	30	50
189	1	30	50
190	1	30	50
191	1	30	50
192	1	30	50
193	1	30	50
194	1	30	50
195	1	30	50
196	1	30	50
197	1	30	50
198	1	30	50
199	1	30	50
200	1	30	50
201	1	30	50
202	1	30	50
203	1	30	50
204	1	30	50
205	1	30	50
206	1	30	50
207	1	33	50
208	1	33	50
209	1	33	50
210	1	33	50
211	1	33	50
212	1	33	50
213	1	33	50
214	1	33	50
215	1	35	50
216	1	35	50
217	1	35	50
218	1	35	52
219	1	35	52
220	1	35	52
221	1	35	52
222	1	35	52
223	1	35	52
224	1	35	54
225	1	37	54
226	1	37	54
227	1	37	54
228	1	37	54
229	1	37	54
230	1	37	54
231	1	37	56
232	1	37	56
233	1	37	56
234	1	39	56
235	1	39	58
236	1	39	58
237	1	39	58
238	1	39	58
239	1	39	58
240	1	42	58
241	1	42	58
242	1	42	58
243	1	42	58
244	1	42	58
245	1	42	58
246	1	42	58
247	1	42	58
248	1	42	58
249	1	42	58
250	1	42	58
251	1	42	58
252	1	42	58
253	1	42	58
254	1	42	58
255	1	42	58
256	1	42	58
257	1	42	58
258	1	42	58
259	1	42	60
260	1	42	60
261	1	42	60
262	1	42	60
263	1	43	60
264	1	43	60
265	1	43	60
266	1	45	60
267	1	45	63
268	1	45	63
269	1	45	63
270	1	45	63
271	1	45	63
272	1	45	63
273	1	45	63
274	1	45	65
275	1	45	65
276	1	45	65
277	1	45	65
278	1	45	66
279	1	45	67
280	1	45	67
281	1	45	67
282	1	45	67
283	1	45	68
284	1	45	69
285	1	45	69
286	1	45	69
287	1	47	69
288	1	47	69
289	1	47	69
290	1	48	69
291	1	49	69
292	1	49	69
293	1	49	69
294	1	49	69
295	1	49	69
296	1	49	71
297	1	49	71
298	1	49	71
299	1	49	72
300	1	49	73
301	1	49	73
302	1	49	73
303	1	49	73
304	1	49	75
305	1	49	75
306	1	49	75
307	1	49	75
308	1	49	75
309	1	49	75
310	1	49	77
311	1	49	77
312	1	49	77
313	1	49	77
314	1	49	77
315	1	49	77
316	1	49	77
317	1	49	77
318	1	49	77
319	1	49	77
320	1	49	77
321	1	49	77
322	1	49	77
323	1	49	77
324	1	49	80
325	1	49	80
326	1	49	80
327	1	52	80
328	1	52	80
329	1	52	80
330	1	54	80
331	1	54	80
332	1	54	82
333	1	56	82
334	1	56	82
335	1	56	82
336	1	56	84
337	1	56	84
338	1	57	84
339	1	57	84
340	1	57	84
341	1	57	84
342	1	57	84
343	1	57	84
344	1	58	84
345	1	58	84
346	1	58	85
347	1	58	86
348	1	58	86
349	1	58	86
350	1	58	86
351	1	58	86
352	1	58	86
353	1	58	87
354	1	58	87
355	1	58	87
356	1	60	87
357	1	60	89
358	1	60	89
359	1	60	89
360	1	60	90
361	1	60	90
362	1	60	90
363	1	60	90
364	1	60	90
365	1	60	90
366	1	60	91
367	1	60	92
368	1	60	92
369	1	60	92
370	1	60	92
371	1	60	92
372	1	60	92
373	1	60	95
374	1	62	95
375	1	62	95
376	1	62	95
377	1	62	95
378	1	62	95
379	1	62	95
380	1	62	95
381	1	62	95
382	1	62	95
383	1	64	95
384	1	64	95
385	1	66	95
386	1	66	95
387	1	66	95
388	1	66	95
389	1	69	95
390	1	69	95
391	1	69	95
392	1	69	95
393	1	69	98
394	1	69	98
395	1	69	98
396	1	69	99
397	1	69	99
398	1	69	99
399	1	71	99
400	1	71	99
401	1	71	99
402	1	71	99
403	1	71	99
404	1	71	99
405	1	71	99
406	1	71	101
407	1	71	101
408	1	71	101
409	1	71	101
410	1	73	101
411	1	73	101
412	1	73	101
413	1	73	103
414	1	73	103
415	1	73	104
416	1	73	104
417	1	73	104
418	1	73	104
419	1	73	104
420	1	73	104
421	1	73	104
422	1	73	104
423	1	73	104
424	1	74	104
425	1	75	104
426	1	75	104
427	1	75	104
428	1	75	104
429	1	75	104
430	1	75	104
431	1	75	104
432	1	75	104
433	1	75	104
434	1	75	104
435	1	75	104
436	1	77	104
437	1	77	107
438	1	77	107
439	1	77	107
440	1	77	109
441	1	77	109
442	1	77	109
443	1	77	109
444	1	77	109
445	1	77	109
446	1	77	110
447	1	77	110
448	1	77	110
449	1	77	110
450	1	77	111
451	1	77	111
452	1	77	111
453	1	77	111
454	1	77	111
455	1	77	111
456	1	77	111
457	1	78	111
458	1	78	111
459	1	78	111
460	1	78	111
461	1	78	111
462	1	78	111
463	1	78	111
464	1	78	111
465	1	78	111
466	1	78	111
467	1	78	112
468	1	78	112
469	1	78	112
470	1	78	113
471	1	78	113
472	1	78	113
473	1	78	116
474	1	78	116
475	1	78	116
476	1	78	116
477	1	78	116
478	1	81	116
479	1	81	116
480	1	81	116
481	1	83	116
482	1	83	116
483	1	83	116
484	1	83	116
485	1	83	116
486	1	83	116
487	1	84	116
488	1	84	116
489	1	84	116
490	1	84	116
491	1	84	118
492	1	87	118
493	1	87	118
494	1	89	118
495	1	89	118
496	1	89	118
497	1	91	118
498	1	91	118
499	1	91	118
500	1	91	118
501	1	91	118
502	1	91	120
503	1	94	120
504	1	94	120
505	1	94	120
506	1	94	120
507	1	94	120
508	1	96	120
509	1	96	120
510	1	96	120
511	1	96	120
512	1	96	120
513	1	96	123
514	1	96	123
515	1	96	123
516	1	96	123
517	1	96	123
518	1	96	125
519	1	96	125
520	1	96	125
521	1	96	125
522	1	96	125
523	1	96	125
524	1	96	125
525	1	96	125
526	1	96	125
527	1	99	125
528	1	99	125
529	2	0	0
530	2	0	0
531	2	0	0
532	2	0	0
533	2	2	0
534	2	2	0
535	2	2	0
536	2	5	0
537	2	5	0
538	2	5	0
539	2	5	0
540	2	5	0
541	2	5	0
542	2	5	0
543	2	5	0
544	2	5	3
545	2	7	3
546	2	7	3
547	2	8	3
548	2	8	5
549	2	8	5
550	2	11	5
551	2	11	5
552	2	11	5
553	2	11	5
554	2	11	5
555	2	11	5
556	2	14	5
557	2	14	5
558	2	14	5
559	2	16	5
560	2	16	5
561	2	16	5
562	2	16	5
563	2	16	5
564	2	16	5
565	2	16	5
566	2	16	5
567	2	16	5
568	2	16	8
569	2	16	8
570	2	17	8
571	2	17	8
572	2	17	8
573	2	17	8
574	2	18	8
575	2	18	8
576	2	18	8
577	2	18	8
578	2	18	8
579	2	18	8
580	2	21	8
581	2	21	10
582	2	21	10
583	2	21	10
584	2	21	10
585	2	21	10
586	2	21	10
587	2	21	10
588	2	21	10
589	2	21	10
590	2	21	10
591	2	21	10
592	2	22	10
593	2	22	12
594	2	22	12
595	2	22	12
596	2	24	12
597	2	24	12
598	2	24	14
599	2	24	14
600	2	24	14
601	2	24	14
602	2	24	14
603	2	24	15
604	2	24	15
605	2	25	15
606	2	26	15
607	2	26	15
608	2	29	15
609	2	29	15
610	2	29	15
611	2	29	15
612	2	31	15
613	2	31	15
614	2	31	15
615	2	33	15
616	2	33	15
617	2	33	16
618	2	33	16
619	2	33	17
620	2	35	17
621	2	35	17
622	2	35	17
623	2	37	17
624	2	37	17
625	2	39	17
626	2	39	17
627	2	39	17
628	2	39	17
629	2	39	17
630	2	39	17
631	2	39	17
632	2	39	19
633	2	39	19
634	2	39	19
635	2	39	19
636	2	39	19
637	2	39	19
638	2	39	19
639	2	39	19
640	2	39	19
641	2	39	19
642	2	39	19
643	2	39	19
644	2	39	19
645	2	39	19
646	2	39	19
647	2	39	19
648	2	39	20
649	2	39	20
650	2	39	20
651	2	39	20
652	2	41	20
653	2	41	20
654	2	41	20
655	2	41	20
656	2	41	20
657	2	41	20
658	2	41	20
659	2	41	20
660	2	41	20
661	2	41	20
662	2	41	20
663	2	41	20
664	2	41	20
665	2	41	20
666	2	41	20
667	2	41	20
668	2	41	20
669	2	41	20
670	2	41	20
671	2	41	20
672	2	41	20
673	2	41	20
674	2	41	23
675	2	41	23
676	2	41	23
677	2	41	23
678	2	41	23
679	2	41	23
680	2	41	23
681	2	41	23
682	2	41	23
683	2	41	23
684	2	41	23
685	2	41	23
686	2	41	23
687	2	41	23
688	2	41	23
689	2	41	23
690	2	41	23
691	2	41	23
692	2	41	23
693	2	41	23
694	2	41	23
695	2	41	25
696	2	41	25
697	2	41	25
698	2	41	25
699	2	41	25
700	2	41	25
701	2	41	25
702	2	41	25
703	2	41	25
704	2	41	25
705	2	41	25
706	2	41	25
707	2	41	25
708	2	41	25
709	2	41	26
710	2	41	27
711	2	41	27
712	2	41	27
713	2	41	30
714	2	41	30
715	2	41	30
716	2	41	30
717	2	41	30
718	2	43	30
719	2	43	30
720	2	44	30
721	2	44	30
722	2	44	31
723	2	44	32
724	2	44	32
725	2	44	32
726	2	44	34
727	2	44	34
728	2	44	34
729	2	44	35
730	2	44	35
731	2	44	35
732	2	44	35
733	2	44	35
734	2	47	35
735	2	47	35
736	2	47	36
737	2	47	36
738	2	47	36
739	2	47	37
740	2	49	37
741	2	49	39
742	2	51	39
743	2	51	39
744	2	51	39
745	2	51	39
746	2	51	39
747	2	51	39
748	2	51	41
749	2	51	41
750	2	51	41
751	2	51	43
752	2	53	43
753	2	53	46
754	2	53	46
755	2	53	46
756	2	53	46
757	2	53	46
758	2	53	46
759	2	53	46
760	2	53	46
761	2	53	46
762	2	53	47
763	2	53	47
764	2	53	48
765	2	53	48
766	2	53	48
767	2	53	50
768	2	56	50
769	2	56	50
770	2	56	50
771	2	56	50
772	2	56	51
773	2	56	51
774	2	56	51
775	2	56	54
776	2	56	53
777	2	56	54
778	2	56	53
779	2	56	54
780	2	56	54
781	2	56	54
782	2	56	54
783	2	56	54
784	2	56	54
785	2	56	54
786	2	56	54
787	2	56	54
788	2	56	54
789	2	56	54
790	2	56	54
791	2	56	54
792	2	59	54
793	2	59	54
794	2	59	54
795	2	61	54
796	2	61	56
797	2	63	56
798	2	63	59
799	2	63	59
800	2	63	59
801	2	63	61
802	2	63	61
803	2	63	61
804	2	63	61
805	2	65	61
806	2	65	63
807	2	68	63
808	2	68	65
809	2	68	65
810	2	69	65
811	2	69	65
812	2	69	65
813	2	69	65
814	2	69	65
815	2	69	65
816	2	69	65
817	2	69	65
818	2	69	68
819	2	69	68
820	2	69	68
821	2	71	68
822	2	71	68
823	2	71	68
824	2	71	68
825	2	71	68
826	2	71	68
827	2	71	68
828	2	71	68
829	2	71	68
830	2	71	68
831	2	71	68
832	2	71	68
833	2	71	68
834	2	71	68
835	2	71	68
836	2	71	68
837	2	71	70
838	2	71	70
839	2	71	70
840	2	71	70
841	2	71	70
842	2	74	70
843	2	74	72
844	2	74	72
845	2	74	72
846	2	74	72
847	2	74	73
848	2	74	73
849	2	74	73
850	2	74	73
851	2	74	73
852	2	74	73
853	2	75	73
854	2	75	75
855	2	75	75
856	2	77	75
857	2	77	75
858	2	77	75
859	2	77	75
860	2	77	75
861	2	77	75
862	2	77	75
863	2	77	75
864	2	77	75
865	2	77	75
866	2	77	75
867	2	77	75
868	2	77	75
869	2	77	75
870	2	77	75
871	2	77	75
872	2	77	75
873	2	77	75
874	2	77	75
875	2	77	75
876	2	77	75
877	2	77	75
878	2	77	75
879	2	77	75
880	2	77	75
881	2	77	75
882	2	77	75
883	2	77	75
884	2	77	75
885	2	77	75
886	2	77	75
887	2	77	75
888	2	77	76
889	2	79	76
890	2	79	76
891	2	81	76
892	2	81	76
893	2	81	76
894	2	82	76
895	2	82	76
896	2	82	76
897	2	82	78
898	2	84	78
899	2	84	78
900	2	84	78
901	2	87	78
902	2	87	78
903	2	87	78
904	2	87	78
905	2	89	78
906	2	89	78
907	2	89	78
908	2	89	78
909	2	89	78
910	2	89	78
911	2	89	80
912	2	91	80
913	2	91	80
914	2	91	80
915	2	91	80
916	2	91	80
917	2	91	80
918	2	91	80
919	2	91	80
920	2	91	82
921	2	91	82
922	2	91	82
923	2	91	85
924	2	91	85
925	2	91	85
926	2	94	85
927	2	94	88
928	2	94	88
929	2	95	88
930	2	96	88
931	2	96	88
932	2	96	88
933	2	96	88
934	2	96	89
935	2	96	90
936	2	99	90
937	2	99	90
938	2	99	90
939	2	99	90
940	2	99	90
941	2	99	90
942	2	99	90
943	2	99	90
944	2	99	91
945	2	99	91
946	2	99	92
947	2	99	92
948	2	99	92
949	2	99	92
950	2	99	92
951	2	99	92
952	2	99	92
953	2	99	92
954	2	101	92
955	2	101	92
956	2	101	92
957	2	101	92
958	2	101	92
959	2	101	92
960	2	104	92
961	2	104	92
962	2	105	92
963	2	105	94
964	2	107	94
965	2	107	94
966	2	107	94
967	2	107	94
968	2	107	94
969	2	107	94
970	2	107	94
971	2	107	94
972	2	107	94
973	2	107	94
974	2	107	94
975	2	109	94
976	2	109	94
977	2	109	94
978	2	109	94
979	2	109	94
980	2	109	94
981	2	109	94
982	2	109	94
983	2	111	94
984	2	111	96
985	2	111	96
986	2	111	96
987	2	111	96
988	2	112	96
989	2	112	96
990	2	112	96
991	2	112	96
992	2	112	96
993	2	112	96
994	2	112	96
995	2	112	96
996	2	112	96
997	2	112	96
998	2	112	96
999	2	112	96
1000	2	112	96
Sheet1

 

[orsm6]

Hi,

I was kindly given a solution to this problem but in trying to apply it to a larger dataset am getting some errors.

what was the original solution?

 

[SubZero55]

This formula here - =LET(Fltr,FILTER($B$12:$C$1972,($A$12:$A$1972=A3)*($B$12:$B$1972>0)),Most,(FILTER(INDEX(Fltr,,1),INDEX(Fltr,,2)=MODE.SNGL(INDEX(Fltr,,2)))),MAX(Most)-MIN(Most))

Ive copied across from the sample sheet where it works - but have made some error in the transfer.

Like most Excel things I am sure there are alternate solutions but struggling to find one here!

 

[orsm6]

This formula here - =LET(Fltr,FILTER($B$12:$C$1972,($A$12:$A$1972=A3)*($B$12:$B$1972>0)),Most,(FILTER(INDEX(Fltr,,1),INDEX(Fltr,,2)=MODE.SNGL(INDEX(Fltr,,2)))),MAX(Most)-MIN(Most))

Ive copied across from the sample sheet where it works - but have made some error in the transfer.

Like most Excel things I am sure there are alternate solutions but struggling to find one here!

my excel doesn't have filter function so can't help on that one, sorry.

 

[SubZero55]

my excel doesn't have filter function so can't help on that one, sorry.

Thanks anyway ORSM...

Ha your username reminds me of one of my all time favourite clips...

David Tua, a heavyweight boxer from New Zealand was on their Celebrity Wheel of Fortune.. and asked for an O for Orsum (how he felt Awesome was spelt).... not even allowed to choose vowels in the game!

 

[Fluff]

How about

=MAX(MAXIFS(B12:B2000,A12:A2000,A3,C12:C2000,SEQUENCE(200))-MINIFS(B12:B2000,A12:A2000,A3,C12:C2000,SEQUENCE(200)))
=MAX(MAXIFS(C12:C2000,A12:A2000,A3,B12:B2000,SEQUENCE(200))-MINIFS(C12:C2000,A12:A2000,A3,B12:B2000,SEQUENCE(200)))

 

[lrobbo314]

Just for kicks, here is a Power Query version.

unique.xlsx
	A	B	C	D	E	F	G
1	Game ID	Away Score	Home Score		Game ID	Away	Home
2	1	0	0		1	11	9
3	1	0	0		2	10	9
4	1	0	0
5	1	0	0
6	1	1	0
7	1	2	0
8	1	2	2
9	1	4	2
10	1	4	5
11	1	4	5
12	1	4	5
13	1	4	7
14	1	4	7
15	1	4	7
16	1	4	10
17	1	4	10
18	1	4	10
19	1	4	10
20	1	4	10
21	1	6	10
22	1	6	10
23	…	…	…
Sheet6

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Game ID", Int64.Type}, {"Away Score", Int64.Type}, {"Home Score", Int64.Type}}),
    GroupAway = Table.Group(Type, {"Game ID"}, {{"Away", each _, type table}}),
    gAway = Table.TransformColumns(GroupAway,{{"Away", each Table.Group(_,{"Away Score"},{{"Count", each _, type table}})}}),
    difAway = Table.TransformColumns(gAway,{{"Away",(tbl)=> Table.Max(Table.TransformColumns(tbl,{{"Count", each Table.Max(_,"Home Score")[Home Score]-Table.Min(_,"Home Score")[Home Score]}}),"Count")[Count]}}),
    GroupHome = Table.Group(Type, {"Game ID"}, {{"Home", each _, type table}}),
    gHome = Table.TransformColumns(GroupHome,{{"Home", each Table.Group(_,{"Home Score"},{{"Count", each _, type table}})}}),
    difHome = Table.TransformColumns(gHome,{{"Home",(tbl)=> Table.Max(Table.TransformColumns(tbl,{{"Count", each Table.Max(_,"Away Score")[Away Score]-Table.Min(_,"Away Score")[Away Score]}}),"Count")[Count]}}),
    Merge = Table.NestedJoin(difAway,{"Game ID"},difHome,{"Game ID"},"difHome",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "difHome", {"Home"}, {"Home"})
in
    Expand