Counting Number of Unique Instances To Work Out Number Of Cycles

PS_Richard

New Member
Joined
Nov 22, 2022
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 001Column 002Column 003Column 004Column 005Column 006
05/01/2022 16:412502202.2925OK31
09/01/2022 16:552502202.2925.5OK32
12/01/2022 15:392502202.2828OK33
18/01/2022 08:582502202.2924.8OK34
21/01/2022 08:272502202.2825.5OK35
24/01/2022 15:162502202.2824.8OK36
27/01/2022 13:202502202.2827.4OK37
30/01/2022 17:452502202.2827.1OK38
01/02/2022 21:162502202.2727.1OK39
04/02/2022 08:072502202.2828.7OK40
07/02/2022 13:502502202.0222.8OK40
12/02/2022 10:162502202.2622.6OK42
15/02/2022 08:052502202.2825.6OK43
17/02/2022 01:302502202.2335.2OK44
20/02/2022 04:052502202.2823.5OK45
22/02/2022 12:372502202.2826.1OK46
25/02/2022 11:272502202.2521.3OK47
28/02/2022 22:032502202.2622.9OK48
04/03/2022 08:502502202.2723.9OK49
07/03/2022 09:542502202.2825.2OK50
10/03/2022 23:542502202.2723.8OK51
14/03/2022 08:202502202.2722.5OK52
16/03/2022 13:102502202.2826.9OK53
19/03/2022 17:582502202.3432.6OK54
23/03/2022 12:582502202.2827.7OK55
26/03/2022 20:342502202.2523.7OK56
31/03/2022 07:092502202.2726.8OK57
06/04/2022 14:442502202.2825.8OK58
12/04/2022 00:012502202.2724.8OK59
16/04/2022 12:112502202.2825.7OK60
19/04/2022 03:492502202.2724.9OK61
25/04/2022 18:212502202.2523.5OK62
28/04/2022 15:352502202.2625.4OK63
04/05/2022 15:142502202.2725.1OK64
08/05/2022 20:452502202.2523.5OK65
13/05/2022 06:372502202.2333.5OK66
25/05/2022 14:372502202.0521.9OK67
08/06/2022 23:482502200.4121.3OK67
08/06/2022 23:532502200.4121.3OK67
26/08/2022 19:432502200.4223.5OK67
26/08/2022 19:452502200.4123.5OK67
26/08/2022 19:482502200.4123.5OK67
26/08/2022 19:502502200.423.5OK67
26/08/2022 19:512502200.4123.5OK67
26/08/2022 19:542502200.4123.5OK67
27/08/2022 05:012502201.2223.1OK67
27/08/2022 08:442502200.4123.2OK67
27/08/2022 08:452502200.4123.2OK67
27/08/2022 08:472502200.4123.2OK67
27/08/2022 08:492502200.4123.2OK67
27/08/2022 23:272502201.7124.5OK67
28/08/2022 18:392502200.4223.9OK67
28/08/2022 18:412502200.4223.9OK67
28/08/2022 18:442502200.4223.9OK67
28/08/2022 18:472502200.4123.9OK67
28/08/2022 18:492502200.4123.9OK67
28/08/2022 18:512502200.4123.9OK67
28/08/2022 18:532502200.4123.9OK67
28/08/2022 18:552502200.4123.9OK67
28/08/2022 18:572502200.4123.9OK67
01/09/2022 00:192502202.1330.4OK67
02/09/2022 07:532502202.1627.7OK67
06/09/2022 18:462502202.2325.5OK68
15/09/2022 15:332502202.2532.9OK69
20/09/2022 16:112502202.2327.4OK70
23/09/2022 13:202502202.2327.7OK71
26/09/2022 14:442502202.2324.8OK72
29/09/2022 00:312502202.2325.8OK73
03/10/2022 07:032502202.2221.3OK74
06/10/2022 00:412502202.2324.5OK75
09/10/2022 11:232502202.2324.5OK76
12/10/2022 19:032502202.2323.5OK77
14/10/2022 17:572502202.0326.1OK77
19/10/2022 14:342502202.2325.5OK79
22/10/2022 07:592502202.2325.8OK80
28/10/2022 12:412502202.2326.9OK81
30/10/2022 13:472502202.2326.8OK82
02/11/2022 07:022502202.2325.8OK83
05/11/2022 01:542502202.2326.1OK84
06/11/2022 21:242502202.2329OK85
20/11/2022 08:282502202.2324.5OK86
22/11/2022 09:202502202.2326.2OK87
25/11/2022 10:022502202.2431OK88
02/12/2022 10:402502202.2222.6OK89
05/12/2022 06:522502202.3426.1OK90
08/12/2022 22:382502202.2221.6OK91
10/12/2022 21:012502202.2326.1OK92
13/12/2022 15:312502202.2324.5OK93


I then have the following on another sheet.

Report v4 - WIP - Amazon.xlsm
KLMNOP
183Min CycleMax CycleTotal Number of Cycles in PeriodNumber of EntriesNumber of Entries Missing
18425022031936288-26
Sheet4
Cell Formulas
RangeFormula
L184L184=MINIFS(eGO_Advanced_Export!$F:$F,eGO_Advanced_Export!$B:$B,$C184)
M184M184=MAXIFS(eGO_Advanced_Export!$F:$F,eGO_Advanced_Export!$B:$B,$C184)
N184N184=$E184-$D184
O184O184=COUNTIFS(eGO_Advanced_Export!B:B,$K184)
P184P184=$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...
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
For O184 how about
Excel Formula:
=COUNT(UNIQUE(FILTER(eGO_Advanced_Export!F:F,eGO_Advanced_Export!B:B=$K184)))
 
Upvote 0
Solution
For O184 how about
Excel Formula:
=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!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,186
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top