Counting Number of Unique Instances To Work Out Number Of Cycles

[PS_Richard]

Good afternoon all.

I am working on a set of data where I need to calculate the number of unique entries over a large set of data with numerous different equipment to see how many cycles each peace of equipment has been through. This is looking over the last 12 months. For my example data here is an extract for one piece of equipment.

Column 001	Column 002	Column 003	Column 004	Column 005	Column 006
05/01/2022 16:41	250220	2.29	25	OK	31
09/01/2022 16:55	250220	2.29	25.5	OK	32
12/01/2022 15:39	250220	2.28	28	OK	33
18/01/2022 08:58	250220	2.29	24.8	OK	34
21/01/2022 08:27	250220	2.28	25.5	OK	35
24/01/2022 15:16	250220	2.28	24.8	OK	36
27/01/2022 13:20	250220	2.28	27.4	OK	37
30/01/2022 17:45	250220	2.28	27.1	OK	38
01/02/2022 21:16	250220	2.27	27.1	OK	39
04/02/2022 08:07	250220	2.28	28.7	OK	40
07/02/2022 13:50	250220	2.02	22.8	OK	40
12/02/2022 10:16	250220	2.26	22.6	OK	42
15/02/2022 08:05	250220	2.28	25.6	OK	43
17/02/2022 01:30	250220	2.23	35.2	OK	44
20/02/2022 04:05	250220	2.28	23.5	OK	45
22/02/2022 12:37	250220	2.28	26.1	OK	46
25/02/2022 11:27	250220	2.25	21.3	OK	47
28/02/2022 22:03	250220	2.26	22.9	OK	48
04/03/2022 08:50	250220	2.27	23.9	OK	49
07/03/2022 09:54	250220	2.28	25.2	OK	50
10/03/2022 23:54	250220	2.27	23.8	OK	51
14/03/2022 08:20	250220	2.27	22.5	OK	52
16/03/2022 13:10	250220	2.28	26.9	OK	53
19/03/2022 17:58	250220	2.34	32.6	OK	54
23/03/2022 12:58	250220	2.28	27.7	OK	55
26/03/2022 20:34	250220	2.25	23.7	OK	56
31/03/2022 07:09	250220	2.27	26.8	OK	57
06/04/2022 14:44	250220	2.28	25.8	OK	58
12/04/2022 00:01	250220	2.27	24.8	OK	59
16/04/2022 12:11	250220	2.28	25.7	OK	60
19/04/2022 03:49	250220	2.27	24.9	OK	61
25/04/2022 18:21	250220	2.25	23.5	OK	62
28/04/2022 15:35	250220	2.26	25.4	OK	63
04/05/2022 15:14	250220	2.27	25.1	OK	64
08/05/2022 20:45	250220	2.25	23.5	OK	65
13/05/2022 06:37	250220	2.23	33.5	OK	66
25/05/2022 14:37	250220	2.05	21.9	OK	67
08/06/2022 23:48	250220	0.41	21.3	OK	67
08/06/2022 23:53	250220	0.41	21.3	OK	67
26/08/2022 19:43	250220	0.42	23.5	OK	67
26/08/2022 19:45	250220	0.41	23.5	OK	67
26/08/2022 19:48	250220	0.41	23.5	OK	67
26/08/2022 19:50	250220	0.4	23.5	OK	67
26/08/2022 19:51	250220	0.41	23.5	OK	67
26/08/2022 19:54	250220	0.41	23.5	OK	67
27/08/2022 05:01	250220	1.22	23.1	OK	67
27/08/2022 08:44	250220	0.41	23.2	OK	67
27/08/2022 08:45	250220	0.41	23.2	OK	67
27/08/2022 08:47	250220	0.41	23.2	OK	67
27/08/2022 08:49	250220	0.41	23.2	OK	67
27/08/2022 23:27	250220	1.71	24.5	OK	67
28/08/2022 18:39	250220	0.42	23.9	OK	67
28/08/2022 18:41	250220	0.42	23.9	OK	67
28/08/2022 18:44	250220	0.42	23.9	OK	67
28/08/2022 18:47	250220	0.41	23.9	OK	67
28/08/2022 18:49	250220	0.41	23.9	OK	67
28/08/2022 18:51	250220	0.41	23.9	OK	67
28/08/2022 18:53	250220	0.41	23.9	OK	67
28/08/2022 18:55	250220	0.41	23.9	OK	67
28/08/2022 18:57	250220	0.41	23.9	OK	67
01/09/2022 00:19	250220	2.13	30.4	OK	67
02/09/2022 07:53	250220	2.16	27.7	OK	67
06/09/2022 18:46	250220	2.23	25.5	OK	68
15/09/2022 15:33	250220	2.25	32.9	OK	69
20/09/2022 16:11	250220	2.23	27.4	OK	70
23/09/2022 13:20	250220	2.23	27.7	OK	71
26/09/2022 14:44	250220	2.23	24.8	OK	72
29/09/2022 00:31	250220	2.23	25.8	OK	73
03/10/2022 07:03	250220	2.22	21.3	OK	74
06/10/2022 00:41	250220	2.23	24.5	OK	75
09/10/2022 11:23	250220	2.23	24.5	OK	76
12/10/2022 19:03	250220	2.23	23.5	OK	77
14/10/2022 17:57	250220	2.03	26.1	OK	77
19/10/2022 14:34	250220	2.23	25.5	OK	79
22/10/2022 07:59	250220	2.23	25.8	OK	80
28/10/2022 12:41	250220	2.23	26.9	OK	81
30/10/2022 13:47	250220	2.23	26.8	OK	82
02/11/2022 07:02	250220	2.23	25.8	OK	83
05/11/2022 01:54	250220	2.23	26.1	OK	84
06/11/2022 21:24	250220	2.23	29	OK	85
20/11/2022 08:28	250220	2.23	24.5	OK	86
22/11/2022 09:20	250220	2.23	26.2	OK	87
25/11/2022 10:02	250220	2.24	31	OK	88
02/12/2022 10:40	250220	2.22	22.6	OK	89
05/12/2022 06:52	250220	2.34	26.1	OK	90
08/12/2022 22:38	250220	2.22	21.6	OK	91
10/12/2022 21:01	250220	2.23	26.1	OK	92
13/12/2022 15:31	250220	2.23	24.5	OK	93

I then have the following on another sheet.

Report v4 - WIP - Amazon.xlsm
	K	L	M	N	O	P
183		Min Cycle	Max Cycle	Total Number of Cycles in Period	Number of Entries	Number of Entries Missing
184	250220	31	93	62	88	-26
Sheet4

Cell Formulas
Range	Formula
L184	L184	=MINIFS(eGO_Advanced_Export!$F:$F,eGO_Advanced_Export!$B:$B,$C184)
M184	M184	=MAXIFS(eGO_Advanced_Export!$F:$F,eGO_Advanced_Export!$B:$B,$C184)
N184	N184	=$E184-$D184
O184	O184	=COUNTIFS(eGO_Advanced_Export!B:B,$K184)
P184	P184	=$F184-$G184

The issue I am facing is that on rows 11 and 12 the cycle in column 6 is both 40, from rows 38 to 63 is 67 and again in rows 73 and 74 I have 77. Due to these extra rows it gives me a total of 88 entries for this piece of equipment but I only need to count the cycle number once in column 6. So taking out those duplicate cycle numbers I am left with a total of 61 entries. This is what I need to show rather than the 88 it currently is so that the difference is only 1 cycle not the -26 I'm seeing now.

Any ideas on how I can achieve this? Unfortunately I'm unable to have the raw data edited to only show one instance as it shows like this whenever the equipment sends an update regardless if used or not.

Thanks in advance...

 

[Fluff]

For O184 how about

=COUNT(UNIQUE(FILTER(eGO_Advanced_Export!F:F,eGO_Advanced_Export!B:B=$K184)))

 

[PS_Richard]

For O184 how about

=COUNT(UNIQUE(FILTER(eGO_Advanced_Export!F:F,eGO_Advanced_Export!B:B=$K184)))

Thanks for the awesomely quick response there Fluff. I was about to type out that for some reason it was giving me the answer of 0 then I realised my mistake of doing a comma instance of the equals sign.

Again, thanks so much for the quick response and for solving this for me!

 

[Fluff]

You're welcome & thanks for the feedback