Formula based on identical dates

[mlarson]

Hi there, I'm hoping you can help me with this problem. In the spreadsheet below I would like an output in column K that is based on values in columns G and H, but only if the date (column A) is identical.

This is probably best explained as an example...
I would like the value in cell K3 to equal (G3-$G$2)/H3. The amount being subtracted should always be the first value for a given date. In this example, cell G2 is the first value in column G for date 9-1-2011. So, the calculation for output in K3 would be (464.44-464.53)/1.25 = -0.080.

Another example would be K8 is equal to (G8-$G$7)/H8. Again, G7 is the subtracted value because it represents the first value in column G for date 9-2-2011.

Although the example below shows only 5 rows for each date my actual spreadsheet will have a large number of rows for each date, and an inconsistent number of rows for each date. This is why I would like this macro to be based on the date (column A).

Columns I and J are intentionally left blank. Cells K2, K7, K12 are blank because they would return an error given the numerator would be zero.

Thanks for any help you can provide.

Here's the spreadsheet (desired output shown in column K):
<table border="0" cellpadding="0" cellspacing="0" width="711"><col style="width: 48pt;" width="64"> <col style="width: 63pt;" width="84"> <col style="width: 48pt;" width="64" span="5"> <col style="width: 53pt;" width="70"> <col style="width: 39pt;" width="52"> <col style="width: 42pt;" width="56"> <col style="width: 49pt;" width="65"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl63" style="width: 63pt;" width="84">B</td> <td class="xl63" style="width: 48pt;" width="64">C</td> <td class="xl63" style="width: 48pt;" width="64">D</td> <td class="xl63" style="width: 48pt;" width="64">E</td> <td class="xl63" style="width: 48pt;" width="64">F</td> <td class="xl63" style="width: 48pt;" width="64">G</td> <td class="xl63" style="width: 53pt;" width="70">H</td> <td class="xl63" style="width: 39pt;" width="52">I</td> <td class="xl63" style="width: 42pt;" width="56">J</td> <td class="xl63" style="width: 49pt;" width="65">K</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2</td> <td class="xl64">9/1/2010</td> <td class="xl65">11:01</td> <td class="xl66">464.20</td> <td class="xl66">464.62</td> <td class="xl66">464.10</td> <td class="xl66">464.54</td> <td class="xl66">1.25</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">3</td> <td class="xl64">9/1/2010</td> <td class="xl65">11:02</td> <td class="xl66">464.18</td> <td class="xl66">464.50</td> <td class="xl66">464.18</td> <td class="xl66">464.44</td> <td class="xl66">1.25</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.080</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">4</td> <td class="xl64">9/1/2010</td> <td class="xl65">11:03</td> <td class="xl66">464.27</td> <td class="xl66">464.50</td> <td class="xl66">464.24</td> <td class="xl66">464.32</td> <td class="xl66">1.25</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.174</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">5</td> <td class="xl64">9/1/2010</td> <td class="xl65">11:04</td> <td class="xl66">464.50</td> <td class="xl66">464.50</td> <td class="xl66">464.07</td> <td class="xl66">464.25</td> <td class="xl66">1.25</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.233</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">6</td> <td class="xl64">9/1/2010</td> <td class="xl65">11:05</td> <td class="xl66">464.21</td> <td class="xl66">464.47</td> <td class="xl66">464.02</td> <td class="xl66">464.15</td> <td class="xl66">1.25</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.312</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">7</td> <td class="xl64">9/2/2010</td> <td class="xl65">11:01</td> <td class="xl66">462.87</td> <td class="xl66">462.94</td> <td class="xl66">462.74</td> <td class="xl66">462.86</td> <td class="xl66">2.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">8</td> <td class="xl64">9/2/2010</td> <td class="xl65">11:02</td> <td class="xl66">462.91</td> <td class="xl66">463.19</td> <td class="xl66">462.66</td> <td class="xl66">462.81</td> <td class="xl66">2.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.020</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">9</td> <td class="xl64">9/2/2010</td> <td class="xl65">11:03</td> <td class="xl66">462.95</td> <td class="xl66">462.99</td> <td class="xl66">462.81</td> <td class="xl66">462.99</td> <td class="xl66">2.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">0.052</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">10</td> <td class="xl64">9/2/2010</td> <td class="xl65">11:04</td> <td class="xl66">463.02</td> <td class="xl66">463.13</td> <td class="xl66">462.96</td> <td class="xl66">463.13</td> <td class="xl66">2.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">0.108</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">11</td> <td class="xl64">9/2/2010</td> <td class="xl65">11:05</td> <td class="xl66">463.13</td> <td class="xl66">463.61</td> <td class="xl66">463.01</td> <td class="xl66">463.61</td> <td class="xl66">2.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">0.300</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">12</td> <td class="xl64">9/3/2010</td> <td class="xl65">11:01</td> <td class="xl66">470.18</td> <td class="xl66">470.23</td> <td class="xl66">469.85</td> <td class="xl66">469.97</td> <td class="xl66">1.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">13</td> <td class="xl64">9/3/2010</td> <td class="xl65">11:02</td> <td class="xl66">469.98</td> <td class="xl66">470.02</td> <td class="xl66">469.66</td> <td class="xl66">470.01</td> <td class="xl66">1.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">0.029</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">14</td> <td class="xl64">9/3/2010</td> <td class="xl65">11:03</td> <td class="xl66">469.82</td> <td class="xl66">469.91</td> <td class="xl66">469.80</td> <td class="xl66">469.84</td> <td class="xl66">1.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.087</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">15</td> <td class="xl64">9/3/2010</td> <td class="xl65">11:04</td> <td class="xl66">469.82</td> <td class="xl66">469.82</td> <td class="xl66">469.63</td> <td class="xl66">469.66</td> <td class="xl66">1.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.210</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">16</td> <td class="xl64">9/3/2010</td> <td class="xl65">11:05</td> <td class="xl66">469.89</td> <td class="xl66">469.98</td> <td class="xl66">469.80</td> <td class="xl66">469.80</td> <td class="xl66">1.50</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl67">-0.113</td> </tr> </tbody></table>

 

[Marcelo Branco]

Hi,

Maybe

Formula in K2 copied down

=IF(COUNTIF($B$2:B2,B2)=1,"",(G2-INDEX($G$2:$G$16,MATCH(B2,$B$2:$B$16,0)))/H2)

M.

 

[mlarson]

Marcelo, that works perfectly! Do you know what the macro would be so I don't have to copy the formula down the entire spreadsheet (I have 100's of thousands of rows)?

 

[Marcelo Branco]

Assuming your data in Sheet1 (adjust to suit), maybe something like that

Sub setFormula()
    Dim lastRow As Long
 
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
 
        .Range("K2:K" & lastRow).Formula = "=IF(COUNTIF($B$2:B2,B2)=1,""""," & _
            "(G2-INDEX($G$2:$G$" & lastRow & ",MATCH(B2,$B$2:$B$" & lastRow & ",0)))/H2)"
    End With
 
End Sub

M.

 

[mlarson]

Marcelo, thank you again. It works very well!

 

[Marcelo Branco]

Marcelo, thank you again. It works very well!

You are welcome and tks for the feedback

(uff.. i was afraid with so many lines it would not work very well)

M.