Kvarner,
Please see the attached.
The heart of this arrangement is in a helper column in column J. For convenience and clarity the “Build” references are moved back to column D by themselves.
The helper is there for 4 reasons:1. It returns the column numbers for each “Build” by MATCHing a continuous lookback in column D to E1:L1.
2. It saves having to repeat this calculation in the main formulae. (columns E:H)
3. It shortens the main formulae.
4. But the main reason is that it allows us to reset the K argument of SMALL back to 1 with each change of “Build” by using a COUNTIF t
hat references the helper values.
The helper formula is entered with a simple Enter and filled down as far as needed
The formula in E82 array entered, filled across to column G and down as far beyond Build 8 as needed is You will get blanks after Build 2 due to the IFERROR.
If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
In H82 this array formula filled down as far as you filled the previous array formula.
RE: the date part. I must confess that I am having difficulty finding a way to tell Excel how to do those match-ups. Formula-wise this may require some hyper-advanced MATCH constructions that are beyond my horizon … unless I am missing something obvious. If this were only two dates relating to two Builds I might be able to pull this one off, but with 3 or more I am afraid I am down-for-the-count.
Bookmarks