Formula Help

[ststern45]

Hi everyone,

Need help with creating a formula:
Digits = 0 through 9
Cells P4 through R4 are the most recent digits Position 1, Position 2, Position 3

Trying to calculate how many rows back when all 3 digits 9, 5, 4 appeared.

So using the example below. The 5 & 9 (from cell range P4 through R4) appeared on line 1. The remaining digit 4 did not appear until line 5 (Cell range P9, Q9, R9)

So the formula needed would give me a value of "5"

So I'm trying to calculate when all 3 digits from P4, Q4, R4 appear from the previous rows.

Note: A cell range could have duplicate digits. For example P4, Q4, R4 could be 9 9 4 or 5 5 5
So even though the digits repeat I would need to count the 1st occurrence of the 1st duplicate digit, 2nd digit of the duplicate or 3rd digit of the duplicate.
So for example if Cell P4 = 9, Q4 = 9, R4 = 6. The result would be: 3.
1st digit 9 appeared on line 1 Cell R5), the second digit 9 appeared on line 2 (cell P6) and the digit 6 appeared on line 3.

Cell P4 = 9
Cell Q4 = 5
Cell R4 = 4

1 Cells P5,Q5,R5 = 3 5 9
2 Cells P6,Q6,R6 = 9 0 3
3 Cells P7,Q7,R7 = 3 6 5
4 Cells P8,Q8,R8 = 6 9 9
5 Cells P9,Q9,R9 = 4 6 3
6 Cells P10,Q10,R10 = 5 7 6
7 Cells P11,Q11,R11 = 4 4 7

Thank you in advance!!

 

[ststern45]

Hi Eric,

I'm using the formula you provided and it works to perfection for 3 digits.
Using the same idea and calculating when just 1 digit appears instead of 3.

Also using the same idea and calculating when 2 digits appears instead of 3.

Have a nice weekend!!

Thanks,
Steve

 

[Eric W]

This formula will tell you the offset where the first value appears:

=IFERROR(SMALL(IF(MMULT(--(COUNTIF(OFFSET(P5:R5,0,0,ROW(P5:P50)-ROW(P5)+1),P4:R4)>=1),{1;1;1}),ROW(P5:P50)-ROW(P5)+1),1),"Not enough found")

with CSE.

The formula that handles 2 items is much trickier due to possible duplicates, and the trick I used for 3 columns won't work for 2. If I figure it out, I'll let you know.

 

[Eric W]

You might be better off with a UDF (User-Defined Function). I wrote one up you can try. To give it a shot, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. In the sheet that opens, paste this code:

Public Function HowFar(ByVal MyRange As Range, ByVal MyCount As Long)
Dim MyData As Variant, i As Long, j As Long, k As Long

    MyData = MyRange.Value
    For i = 2 To UBound(MyData)
        For j = 1 To 3
            For k = 1 To 3
                If MyData(i, j) = MyData(1, k) Then
                    MyCount = MyCount - 1
                    MyData(1, k) = "x"
                    If MyCount = 0 Then
                        HowFar = i - 1
                        Exit Function
                    End If
                    Exit For
                End If
            Next k
        Next j
    Next i
    
    HowFar = "Not enough found"
End Function

Press Alt-Q to close the editor. Now you can use the function as shown:

	A	B	C	K	L	M	N	O	P	Q	R	S	AA	AB	AC	AD
1st match	2nd match	3rd match
Not enough found
Not enough found
Not enough found	Not enough found
Not enough found	Not enough found	Not enough found
Not enough found	Not enough found	Not enough found

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6/26/2007[/TD]
[TD="align: right"]954[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/26/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]954[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6/25/2007[/TD]
[TD="align: right"]359[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/25/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]359[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6/24/2007[/TD]
[TD="align: right"]903[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/24/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]903[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/23/2007[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/23/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]365[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6/22/2007[/TD]
[TD="align: right"]699[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/22/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]699[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6/19/2007[/TD]
[TD="align: right"]463[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/19/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]463[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6/19/2007[/TD]
[TD="align: right"]576[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/19/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]576[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6/19/2007[/TD]
[TD="align: right"]447[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/19/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]447[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6/18/2007[/TD]
[TD="align: right"]872[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/18/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]872[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6/17/2007[/TD]
[TD="align: right"]822[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/17/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]822[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6/16/2007[/TD]
[TD="align: right"]276[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/16/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]276[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]6/15/2007[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/15/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6/14/2007[/TD]
[TD="align: right"]685[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/14/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]685[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]6/13/2007[/TD]
[TD="align: right"]869[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/13/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]869[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]6/12/2007[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/12/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]320[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6/11/2007[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/11/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]6/10/2007[/TD]
[TD="align: right"]674[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/10/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]674[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]6/9/2007[/TD]
[TD="align: right"]389[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/9/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]389[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]6/8/2007[/TD]
[TD="align: right"]262[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/8/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]262[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]6/7/2007[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/7/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]224[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]6/6/2007[/TD]
[TD="align: right"]785[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/6/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]785[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]6/5/2007[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/5/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]999[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]6/4/2007[/TD]
[TD="align: right"]247[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/4/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]247[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]6/3/2007[/TD]
[TD="align: right"]603[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/3/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]603[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]6/2/2007[/TD]
[TD="align: right"]356[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/2/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]356[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]6/1/2007[/TD]
[TD="align: right"]689[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/1/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]689[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]5/31/2007[/TD]
[TD="align: right"]968[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/31/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]968[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]5/30/2007[/TD]
[TD="align: right"]712[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/30/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]712[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]5/29/2007[/TD]
[TD="align: right"]694[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/29/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]694[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]5/28/2007[/TD]
[TD="align: right"]955[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/28/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]955[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]5/27/2007[/TD]
[TD="align: right"]813[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/27/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]813[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]5/26/2007[/TD]
[TD="align: right"]473[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/26/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]473[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]5/25/2007[/TD]
[TD="align: right"]439[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/25/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]439[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]5/24/2007[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/24/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]163[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]5/23/2007[/TD]
[TD="align: right"]485[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/23/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]485[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]5/22/2007[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/22/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]5/21/2007[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/21/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]62[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]5/20/2007[/TD]
[TD="align: right"]935[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/20/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]935[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]5/19/2007[/TD]
[TD="align: right"]889[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/19/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]889[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]5/18/2007[/TD]
[TD="align: right"]932[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/18/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]932[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]5/17/2007[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/17/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]71[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]5/16/2007[/TD]
[TD="align: right"]529[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/16/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]529[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]5/15/2007[/TD]
[TD="align: right"]886[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/15/2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"]886[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AA4[/TH]
[TD="align: left"]=howfar($P4:$R50,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AB4[/TH]
[TD="align: left"]=howfar($P4:$R50,2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AC4[/TH]
[TD="align: left"]=howfar($P4:$R50,3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ststern45]

Excellent Eric.
Works to perfection.
Thank you so much.
Steve

 

[Eric W]

Happy to help.