Spreadsheet: 679841d1590654785-formula-for-concurrent-activity-example-1-
Sheet: Example

Row | Activity | Platform | Feature | User | Machine | COUNT |  | Feature A | Feature B | Feature C | Feature D | Feature E | Feature F | Feature G | Feature H | Feature I | Machine A | Machine B |  | In: 0 |  |  |  | First off, to me this seems complex, so I'll be surprised if we can do this...but here we go!
 |  |  |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  | Out: 1
1 | OUT | Platform A | Feature G | User A | Machine A | 1
2 | OUT | Platform A | Feature E | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I have a data extract which shows the check in and check out activity of certain program features by machine. The data sets are large (500K+ lines) and I'm trying to see if there is anyway to determine the peak number of machines which had a feature checked out.
3 | IN | Platform A | Feature E | User A | Machine A | 1
4 | IN | Platform A | Feature G | User A | Machine A | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | In short, here is some of the logic it will need:
5 | OUT | Platform A | Feature G | User A | Machine A | 1
6 | OUT | Platform A | Feature E | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - Each individual machine, could have multiple features checked out.
7 | out | Platform A | Feature G | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - The count will need to keep track of each feature on each unique machine to determine if it increases or decreases the count. One the first feature is checked out on that machine, the count increases by 1. Once all Features on the machine has been zeroed out, meaning there are no more active features checked out for that machine, then it can reduce the count by 1
8 | out | Platform A | Feature G | User A | Machine A | 1
9 | out | Platform A | Feature E | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Some additional details:
10 | in | Platform A | Feature E | User A | Machine A | 1
11 | in | Platform A | Feature G | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - The log shows activity in chronological order by each row per the "Line" column.
12 | OUT | Platform A | Feature I | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - The value "OUT" in all caps means that the feature was actually checked out for that machine.
13 | in | Platform A | Feature G | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - The value "IN" in all caps means that the feature was actually checked in (returned) for that machine.
14 | out | Platform A | Feature I | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - There are "out" and "in" values which are just "soft checks". These activity lines should have no impact other than saying the feature is still in use.
15 | out | Platform A | Feature I | User A | Machine A | 1
16 | IN | Platform A | Feature E | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I have attached an image of sample data and me manually populating the COUNT column (column F) to show what the formula results should be. I have color coded the rows based upon the individual machines so you can see that over the course of the 30 lines, you'll see activity switching back and forth from individual machines. The logic behind my counting and what the formula will need to account for:
17 | IN | Platform A | Feature G | User A | Machine A | 1
18 | in | Platform A | Feature I | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - Row 1-4: Feature B is checked out, count goes to 1. Feature A is checked out, count stays at 1 because it is on the same machine. Feature A is checked in, count stays at 1 because Feature B is still active. Feature B is checked in, count goes to 0 because all features on that machine are now checked in.
19 | out | Platform A | Feature I | User A | Machine A | 1
20 | in | Platform A | Feature I | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - Row 5-9: Feature B is checked out, count goes to 1. Feature A is checked out, count stays at 1 because it is on the same machine. We see soft check ins and check outs (rows 6, 8 and 9) and nothing will change.
21 | in | Platform A | Feature I | User A | Machine A | 1
22 | IN | Platform A | Feature I | User A | Machine A | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - Row 10-14: Feature B is checked out on a new machine (Machine A) while the features are still checked out on the previous machine (Machine C). As such, count increases from 1 active machine to 2 active machines. In Row 15, Feature C is checked out on a new machine (Machine B) while the features for Machine A and C are still active, count increases from 2 active machines to 3 active machines.
23 | OUT | Platform A | Feature I | User A | Machine A | 1
24 | OUT | Platform A | Feature B | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - Row 15-21: These are all soft check in and check outs. No impact on the count.
25 | OUT | Platform A | Feature D | User A | Machine A | 1
26 | OUT | Platform A | Feature C | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - Row 22-23: Feature B and A are checked out on [NAME] Although these are new features for Machine B, Machine B already had an active check out (Feature C on row 14) so this has no impact. The count stays the same.
27 | OUT | Platform A | Feature G | User A | Machine A | 1
28 | OUT | Platform A | Feature F | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | - Row 24-30: These rows are the check ins of all the features on each machine. Remember, the count should not decrease until ALL features on the individual machine has been checked in. That is why for Machine B, the decrease count for this machine doesn't occur until Row 30 when the last feature for that machine is checked in (Feature check ins occur on Row 24, 29 and 30 for Machine B).
29 | IN | Platform A | Feature I | User A | Machine A | 1
30 | IN | Platform A | Feature F | User A | Machine A | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I have uploaded the example workbook. Let me know if there are any other questions but I'm hoping there is some way to formula this or analyze this somehow instead of manually.
31 | OUT | Platform A | Feature H | User A | Machine A | 1
32 | IN | Platform A | Feature B | User A | Machine A | 1
33 | IN | Platform A | Feature D | User A | Machine A | 1
34 | IN | Platform A | Feature C | User A | Machine A | 1
35 | IN | Platform A | Feature G | User A | Machine A | 1
36 | IN | Platform A | Feature H | User A | Machine A | 0
37 | OUT | Platform A | Feature G | User A | Machine A | 1
38 | OUT | Platform A | Feature E | User A | Machine A | 1
39 | IN | Platform A | Feature E | User A | Machine A | 1
40 | IN | Platform A | Feature G | User A | Machine A | 0
41 | OUT | Platform A | Feature I | User A | Machine A | 1
42 | OUT | Platform A | Feature I | User A | Machine A | 1
43 | OUT | Platform A | Feature D | User A | Machine A | 1
44 | OUT | Platform A | Feature D | User A | Machine A | 1
45 | OUT | Platform A | Feature I | User A | Machine A | 1
46 | OUT | Platform A | Feature I | User A | Machine A | 1
47 | IN | Platform A | Feature I | User A | Machine A | 1
48 | IN | Platform A | Feature D | User A | Machine A | 0
49 | OUT | Platform A | Feature G | User A | Machine A | 1
50 | out | Platform A | Feature G | User A | Machine A | 1
51 | out | Platform A | Feature G | User A | Machine A | 1
52 | OUT | Platform A | Feature E | User A | Machine A | 1
53 | IN | Platform A | Feature E | User A | Machine A | 1
54 | in | Platform A | Feature G | User A | Machine A | 1
55 | in | Platform A | Feature G | User A | Machine A | 1
56 | IN | Platform A | Feature G | User A | Machine A | 0
57 | OUT | Platform A | Feature G | User A | Machine A | 1
58 | OUT | Platform A | Feature E | User A | Machine A | 1
59 | IN | Platform A | Feature E | User A | Machine A | 1
60 | IN | Platform A | Feature G | User A | Machine A | 0
61 | OUT | Platform A | Feature I | User A | Machine A | 1
62 | OUT | Platform A | Feature H | User A | Machine A | 1
63 | IN | Platform A | Feature H | User A | Machine A | 1
64 | OUT | Platform A | Feature A | User A | Machine A | 1
65 | IN | Platform A | Feature I | User A | Machine A | 1
66 | OUT | Platform A | Feature G | User A | Machine A | 1
67 | OUT | Platform A | Feature E | User A | Machine A | 1
68 | IN | Platform A | Feature E | User A | Machine A | 1
69 | IN | Platform A | Feature G | User A | Machine A | 1
70 | OUT | Platform A | Feature G | User A | Machine A | 1
71 | OUT | Platform A | Feature E | User A | Machine A | 1
72 | IN | Platform A | Feature E | User A | Machine A | 1
73 | IN | Platform A | Feature G | User A | Machine A | 1
74 | IN | Platform A | Feature A | User A | Machine A | 0
75 | OUT | Platform A | Feature I | User A | Machine A | 1
76 | OUT | Platform A | Feature H | User A | Machine A | 1
77 | OUT | Platform A | Feature G | User A | Machine A | 1
78 | OUT | Platform A | Feature E | User A | Machine A | 1
79 | out | Platform A | Feature G | User A | Machine A | 1
80 | out | Platform A | Feature E | User A | Machine A | 1
81 | IN | Platform A | Feature H | User A | Machine A | 1
82 | in | Platform A | Feature E | User A | Machine A | 1
83 | IN | Platform A | Feature E | User A | Machine A | 1
84 | in | Platform A | Feature G | User A | Machine A | 1
85 | IN | Platform A | Feature G | User A | Machine A | 1
86 | IN | Platform A | Feature I | User A | Machine A | 0
87 | OUT | Platform A | Feature I | User A | Machine B | 1
88 | OUT | Platform A | Feature I | User A | Machine B | 1
89 | OUT | Platform A | Feature G | User A | Machine B | 1
90 | OUT | Platform A | Feature G | User A | Machine B | 1
91 | OUT | Platform A | Feature E | User A | Machine B | 1
92 | OUT | Platform A | Feature E | User A | Machine B | 1
93 | IN | Platform A | Feature G | User A | Machine B | 1
94 | IN | Platform A | Feature E | User A | Machine B | 1
95 | OUT | Platform A | Feature I | User A | Machine A | 2
96 | OUT | Platform A | Feature I | User A | Machine A | 2
97 | OUT | Platform A | Feature D | User A | Machine A | 2
98 | OUT | Platform A | Feature D | User A | Machine A | 2
99 | OUT | Platform A | Feature I | User A | Machine A | 2
100 | OUT | Platform A | Feature I | User A | Machine A | 2
101 | IN | Platform A | Feature I | User A | Machine A | 2
102 | IN | Platform A | Feature D | User A | Machine A | 1
103 | out | Platform A | Feature I | User A | Machine B | 1
104 | in | Platform A | Feature I | User A | Machine B | 1
105 | OUT | Platform A | Feature G | User A | Machine A | 2
106 | OUT | Platform A | Feature E | User A | Machine A | 2
107 | out | Platform A | Feature G | User A | Machine A | 2
108 | out | Platform A | Feature E | User A | Machine A | 2
109 | in | Platform A | Feature E | User A | Machine A | 2
110 | in | Platform A | Feature G | User A | Machine A | 2
111 | out | Platform A | Feature G | User A | Machine A | 2
112 | out | Platform A | Feature E | User A | Machine A | 2
113 | in | Platform A | Feature E | User A | Machine A | 2
114 | in | Platform A | Feature G | User A | Machine A | 2
115 | out | Platform A | Feature G | User A | Machine A | 2
116 | out | Platform A | Feature E | User A | Machine A | 2
117 | in | Platform A | Feature E | User A | Machine A | 2
118 | in | Platform A | Feature G | User A | Machine A | 2
119 | out | Platform A | Feature G | User A | Machine A | 2
120 | out | Platform A | Feature E | User A | Machine A | 2
121 | out | Platform A | Feature G | User A | Machine A | 2
122 | out | Platform A | Feature E | User A | Machine A | 2
123 | in | Platform A | Feature E | User A | Machine A | 2
124 | in | Platform A | Feature G | User A | Machine A | 2
125 | IN | Platform A | Feature I | User A | Machine B | 1