Is this even possible? Calculate frequency differences between columns if several other columns match

[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="class: cms_table, width: 713"]
<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="class: cms_table, width: 756"]
<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!

 

[rallcorn]

Duplicate:
http://www.mrexcel.com/forum/excel-...between-two-columns-based-other-criteria.html