Help with a macro that will determine the difference between two columns based on other criteria

[viralnerd2013]

Hello,

I am trying to develop a macro that will identify sites that are increasing or decreasing in frequency over time. I want to subtract the Freq at the SCREENING time point from the later time points (WEEK) for all rows where the Subj, Pos, Ref, and Sub are the same. If a value is present at any WEEK that does not have a corresponding SCREENING line, then the SCREENING Freq would be zero. If the Freq at a WEEK time point is less than FREQ at Screening then a negative number should be reported.

Basically, I want to convert this table:
[TABLE="width: 713"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Subj[/TD]
[TD]Var1[/TD]
[TD]Sample[/TD]
[TD]Pos[/TD]
[TD]Var2[/TD]
[TD]Ref[/TD]
[TD]Sub[/TD]
[TD]Var3[/TD]
[TD]Freq[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]23212[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]11230[/TD]
[TD="align: right"]0.48[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]20702[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]18170[/TD]
[TD="align: right"]0.88[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]23018[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]20003[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]19876[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]17337[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]29728[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]25910[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK72[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]28397[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]24845[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]23224[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]11268[/TD]
[TD="align: right"]0.49[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]20704[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]18253[/TD]
[TD="align: right"]0.88[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]23022[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]20094[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]19890[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]17425[/TD]
[TD="align: right"]0.88[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]29744[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]25997[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK72[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]28413[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]24973[/TD]
[TD="align: right"]0.88[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]2390[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]2149[/TD]
[TD="align: right"]0.90[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]2163[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]1903[/TD]
[TD="align: right"]0.88[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]1225[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]946[/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]500[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]0.26[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]3315[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]3260[/TD]
[TD="align: right"]0.98[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]3275[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]3199[/TD]
[TD="align: right"]0.98[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]1518[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]1456[/TD]
[TD="align: right"]0.96[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]441[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]0.87
[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]K[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]0.10[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD="align: right"]2344[/TD]
[TD="align: right"]0.10[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]K[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]0.10[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD="align: right"]2344[/TD]
[TD="align: right"]0.10[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]R[/TD]
[TD="align: right"]4688[/TD]
[TD="align: right"]0.20[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]R[/TD]
[TD="align: right"]9360[/TD]
[TD="align: right"]0.40[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD="align: right"]2344[/TD]
[TD="align: right"]0.10
[/TD]
[/TR]
</tbody>[/TABLE]

To this table:
[TABLE="width: 756"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Subj[/TD]
[TD]Var1[/TD]
[TD]Sample[/TD]
[TD]Pos[/TD]
[TD]Var2[/TD]
[TD]Ref[/TD]
[TD]Sub[/TD]
[TD]Var3[/TD]
[TD]Freq[/TD]
[TD]Diff[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]23212[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]11230[/TD]
[TD="align: right"]0.48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]20702[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]18170[/TD]
[TD="align: right"]0.88[/TD]
[TD="align: right"]0.40[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]23018[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]20003[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]0.39[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]19876[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]17337[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]0.39[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]29728[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]25910[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]0.39[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK72[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]28397[/TD]
[TD]K[/TD]
[TD]R[/TD]
[TD="align: right"]24845[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]0.39[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]23224[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]11268[/TD]
[TD="align: right"]0.49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]20704[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]18253[/TD]
[TD="align: right"]0.88[/TD]
[TD="align: right"]0.39[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]23022[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]20094[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]0.41[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]19890[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]17425[/TD]
[TD="align: right"]0.88[/TD]
[TD="align: right"]0.39[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]29744[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]25997[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]0.41[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK72[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]28413[/TD]
[TD]G[/TD]
[TD]V[/TD]
[TD="align: right"]24973[/TD]
[TD="align: right"]0.88[/TD]
[TD="align: right"]0.39[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]2390[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]2149[/TD]
[TD="align: right"]0.90[/TD]
[TD="align: right"]0.90[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]2163[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]1903[/TD]
[TD="align: right"]0.88[/TD]
[TD="align: right"]0.88[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]1225[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]946[/TD]
[TD="align: right"]0.77[/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]500[/TD]
[TD]S[/TD]
[TD]R[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]0.26[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]3315[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]3260[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.98[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]3275[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]3199[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.98[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK24[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]1518[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]1456[/TD]
[TD="align: right"]0.96[/TD]
[TD="align: right"]0.96[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK36[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]441[/TD]
[TD]R[/TD]
[TD]K[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]K[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]0.10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]SCREENING[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD="align: right"]2344[/TD]
[TD="align: right"]0.10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]K[/TD]
[TD="align: right"]2342[/TD]
[TD="align: right"]0.10[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD="align: right"]2344[/TD]
[TD="align: right"]0.10[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK16[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]R[/TD]
[TD="align: right"]4688[/TD]
[TD="align: right"]0.20[/TD]
[TD="align: right"]0.10[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]R[/TD]
[TD="align: right"]9360[/TD]
[TD="align: right"]0.40[/TD]
[TD="align: right"]0.30[/TD]
[/TR]
[TR]
[TD]ABC123-0324[/TD]
[TD]test[/TD]
[TD]WEEK20[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]22342[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD="align: right"]2344[/TD]
[TD="align: right"]0.10[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts on how to do this?

Thank you!

 

[J.Ty.]

I have devised a solution with a formula, not macro.

Please test it carefully, because it does not always agree with the sample results in your second sheet. However, as far as I understand, it does implement exactly your description. Therefore I believe that you have made mistakes computing manually sample results. The formulas in column L test if my formula gives the same result as your value. In the rows below the fragment I post here it sometimes reports FALSE.

	A	B	C	D	E	F	G	H	I	J	K	L
Subj	Var1	Sample	Pos	Var2	Ref	Sub	Var3	Freq	Diff
ABC123-0324	test	SCREENING	K	R
ABC123-0324	test	WEEK16	K	R
ABC123-0324	test	WEEK20	K	R
ABC123-0324	test	WEEK24	K	R
ABC123-0324	test	WEEK36	K	R
ABC123-0324	test	WEEK72	K	R
ABC123-0324	test	SCREENING	G	V

<colgroup></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]

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

[TD="align: right"]11230[/TD]
[TD="align: right"]0,48[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]

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

[TD="align: right"]18170[/TD]
[TD="align: right"]0,88[/TD]
[TD="align: right"]0,4[/TD]
[TD="align: right"]0,4[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]

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

[TD="align: right"]20003[/TD]
[TD="align: right"]0,87[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]

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

[TD="align: right"]17337[/TD]
[TD="align: right"]0,87[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]

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

[TD="align: right"]25910[/TD]
[TD="align: right"]0,87[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]

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

[TD="align: right"]24845[/TD]
[TD="align: right"]0,87[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]0,39[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]

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

[TD="align: right"]11268[/TD]
[TD="align: right"]0,49[/TD]
[TD="align: right"][/TD]

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

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=IF(EXACT(C2,"SCREENING"),"",I2-SUMIFS($I:$I,$A:$A,A2,$C:$C,"SCREENING",$D:$D,D2,$F:$F,F2,$G:$G,G2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L2[/TH]
[TD="align: left"]=J2=K2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K3[/TH]
[TD="align: left"]=IF(EXACT(C3,"SCREENING"),"",I3-SUMIFS($I:$I,$A:$A,A3,$C:$C,"SCREENING",$D:$D,D3,$F:$F,F3,$G:$G,G3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L3[/TH]
[TD="align: left"]=J3=K3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Best,

J.Ty.

 

[viralnerd2013]

Thank you so much, J.Ty. You are correct, the formula performs better than my brain! It works beautifully.

You just made my life a lot easier!!

 

[J.Ty.]

That's great pleasure for me!

Good luck!

J.Ty.