Formula for sequential numbering based on rules

Question

I have a very complex Excel formula that I need to create, but I keep getting stuck. I have data that looks like this:

Date         ID   Focal  Time      Condition
08/22/2014   FJ   1      7:52:00   T
08/22/2014   FJ   1      7:52:00   T
08/22/2014   FJ   1      7:52:00   T
08/22/2014   RJ   1      8:52:00   T
08/22/2014   FJ   2      9:52:00   T
08/22/2014   FJ   2      9:52:00   T
08/22/2014   FJ   2      9:52:00   T
08/22/2014   FJ   2      9:52:00   T
08/22/2014   FJ   3      10:52:00  T
08/22/2014   FJ   3      10:52:00  T
08/22/2014   FJ   1      11:32:00  C
08/22/2014   FJ   1      11:32:00  C
08/22/2014   RJ   2      11:52:00  T
08/22/2014   RJ   2      11:52:00  T
08/22/2014   FJ   4      10:52:00  T
08/22/2014   FJ   4      10:52:00  T
08/22/2014   MD   1      12:52:00  T
08/22/2014   RJ   3      13:52:00  T
08/22/2014   RJ   3      13:52:00  T 
08/22/2014   FJ   2      14:52:00  C 
08/23/2014   FJ   1      7:52:00   T
08/23/2014   FJ   1      7:52:00   T
08/23/2014   RJ   1      8:52:00   T
08/23/2014   FJ   1      9:52:00   C

I need the Focal number column to autopopulate according to the following rules:

The first entry will always be "1".
Each ID should have the same Focal number when their Date, Time, and Condition are the same.
When only the time changes, the number should be sequentially listed.
When the condition changes, the number should start over at 1, and then these should be sequential when the time changes.
If the condition changes to an ID that has previously experienced it, the sequential number should pick up when it left off.
Also, when the ID changes, the number should start over at one.
Finally, a new date will start this process for all IDs all over again.

I doubt you will get a formula to do this. Have you considered VBA? — chris neilsen, Oct 27 '14 at 06:13
@mermaid-niki Why is the last row's Focal 2, not 1? New date starts over again, condition change starts over at 1... This is the first C for FJ on 08/23... — Captain, Oct 27 '14 at 08:00
@chrisneilsen: [Famous last words](http://stackoverflow.com/q/5504797/119775) — Jean-François Corbett, Oct 27 '14 at 08:57
Sorry - the last one should be a "1". (Just fixed that) --- I consider my problem solved. You are all amazing. Thank you! =) — Mermaid Niki, Oct 27 '14 at 09:41

score 2 · Accepted Answer · answered Oct 27 '14 at 08:48

I have this solution as an array formula (enter it using Ctrl+Shift+Enter) e.g. in C2:

=IF(COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2,$E$1:$E1,$E2)=0,1,MAX(IF(($A$1:$A1=$A2)*($B$1:$B1=$B2)*($E$1:$E1=$E2),$C$1:$C1,0)+IF(COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2,$E$1:$E1,$E2,$D$1:$D1,$D2)>0,0,1)))

This is looking at all the rows above the current one (e.g. $A$1:$A1 locks the start as the top and the end as the row above)
If there is now matching date, ID & condition in the rows above --> 1
Otherwise, use the array formula part to find the MAX Focal for that date/ID/condition and, if it is a new time, add 1

Hope this is a clear enough explanation...

It seems to give two differences to your example... The final row (as per my comment) and also FJ/T/10:%2 with Focal 4... that combination already exists.

Good luck, and remember to enter it using Ctrl+Shift+Enter

Nice solution, I was a bit too quick to dismiss a single formula as being too complicated! — Dave, Oct 27 '14 at 08:54

score 1 · Answer 2 · answered Oct 27 '14 at 08:48

I have something, but I haven't been able to do it as a single formula. Enter the following in row 2 and copy the formula down:

In column F:

=A2&B2&D2&E2    ' Concatenate the required fields to create a reference

In Column G:

=IF(COUNTIF($F$2:F2,F2)>1,"",1)    ' Create a marker to determine the first occurrence of each reference

In column H:

=IF(G2=1,A2&B2&E2,"")    ' Create a reference to count occurrences in a day

In column I:

=IF(G2=1,COUNTIF($H$2:H2,H2),0)    ' Count first occurrences in the day

In column J:

=INDEX($I$2:I2,MATCH(F2,$F$2:F2,0))    ' Return the count of first occurrences

It can be tidied up quite a bit but I think it does what you are asking for and this was the simplest way to show what I did.

Formula for sequential numbering based on rules

2 Answers2