In advance I appreciate any help. I spent about 12 hours on this yesterday and while I almost had it, it's not quite good enough
Excel 2010 - I have two data sources that I need to compare (50,000 lines in one & 150,000 in the other), they are both in pivot tables with the same field headings. I need to compare the data from both sources and end up with a variance report. I read somewhere it worked better to put them into one table. So I did that but I can't get the formula right.
=IF(AND($A4<>$A3,$B4<>$B3),"",IF(AND($D4=$D3,$E4<>$E3),ROUND(F4-F3,3),"")) - This formula above is in column H & copied to I.
If the client and product are the same I need to compare the acquisition date from the both, if that's the same, I need the variance of the quantity & total cost. This seems to work fine if there is an external & internal source for the same acquisition date for the same client. However, if there is only one line item for the client or if there is either only an internal or external data source for a particular product it's not picking it up. I'm sure it's because I'm excluding any that don't have the same client in the first formula but I'm not sure how else to do it. I've tried many variations & still end up with a variance report that's not quite complete. The items in yellow aren't working correctly.
I've searched for this but almost all are only comparing 1-2 columns & not calculating anything on the data.
Would it be better to compare pivot tables or do this entirely different?
Once I have the original pass on this & all variances are dealt with, I will need to run this on a weekly basis at a minimum but preferably daily.
Again, I appreciate any help.
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 118px"><COL style="WIDTH: 90px"><COL style="WIDTH: 325px"><COL style="WIDTH: 96px"><COL style="WIDTH: 64px"><COL style="WIDTH: 84px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Client/Account</TD><TD>Product #</TD><TD style="BACKGROUND-COLOR: #ccffcc">Product</TD><TD>ACQ DATE</TD><TD>Source</TD><TD>QTY.</TD><TD>TOTAL COST.</TD><TD style="BACKGROUND-COLOR: #ccffcc">Quantity Variance</TD><TD style="BACKGROUND-COLOR: #ccffcc">Cost Variance</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">A</TD><TD style="BACKGROUND-COLOR: #ffff00">IBM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1/17/2012</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">13,300.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">134,483.00</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">3/16/2011</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">350.000</TD><TD style="TEXT-ALIGN: right">21,300.13</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">3/16/2011</TD><TD>External</TD><TD style="TEXT-ALIGN: right">22.000</TD><TD style="TEXT-ALIGN: right">1,338.87</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">(328.000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">(19,961.260)</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">5/4/2011</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">165.000</TD><TD style="TEXT-ALIGN: right">10,989.99</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">5/4/2011</TD><TD>External</TD><TD style="TEXT-ALIGN: right">165.000</TD><TD style="TEXT-ALIGN: right">10,989.99</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">B</TD><TD style="BACKGROUND-COLOR: #ffff00">APPLE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/2/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">160.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">10,914.80</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1/12/1970</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">462.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8,195.88</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1/3/1979</TD><TD style="BACKGROUND-COLOR: #ffff00">Internal</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">393.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6,630.38</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">12/31/2010</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3,338.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.00</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">57</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 2</TD><TD style="BACKGROUND-COLOR: #ffff00">D</TD><TD style="BACKGROUND-COLOR: #ffff00">BHP</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5/3/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Internal</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3,023.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">253,468.88</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">58</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 2</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2/8/1993</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">21.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5.12</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">63</TD><TD>Client 2</TD><TD>E</TD><TD style="BACKGROUND-COLOR: #ccffcc">MICROSOFT</TD><TD style="TEXT-ALIGN: right">9/1/2010</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">3,248.000</TD><TD style="TEXT-ALIGN: right">50,473.92</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">64</TD><TD>Client 2</TD><TD>E</TD><TD style="BACKGROUND-COLOR: #ccffcc">MICROSOFT</TD><TD style="TEXT-ALIGN: right">9/1/2010</TD><TD>External</TD><TD style="TEXT-ALIGN: right">3,248.000</TD><TD style="TEXT-ALIGN: right">50,473.92</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H2</TD><TD>=IF(AND($A2<>$A1,$B2<>$B1),"",IF(AND($D2=$D1,$E2<>$E1),ROUND(F2-F1,3),""))</TD></TR><TR><TD>I2</TD><TD>=IF(AND($A2<>$A1,$B2<>$B1),"",IF(AND($D2=$D1,$E2<>$E1),ROUND(G2-G1,2),""))</TD></TR><TR><TD>H3</TD><TD>=IF(AND($A3<>$A2,$B3<>$B2),"",IF(AND($D3=$D2,$E3<>$E2),ROUND(F3-F2,3),""))</TD></TR><TR><TD>I3</TD><TD>=IF(AND($A3<>$A2,$B3<>$B2),"",IF(AND($D3=$D2,$E3<>$E2),ROUND(G3-G2,2),""))</TD></TR><TR><TD>H4</TD><TD>=IF(AND($A4<>$A3,$B4<>$B3),"",IF(AND($D4=$D3,$E4<>$E3),ROUND(F4-F3,3),""))</TD></TR><TR><TD>I4</TD><TD>=IF(AND($A4<>$A3,$B4<>$B3),"",IF(AND($D4=$D3,$E4<>$E3),ROUND(G4-G3,2),""))</TD></TR><TR><TD>H5</TD><TD>=IF(AND($A5<>$A4,$B5<>$B4),"",IF(AND($D5=$D4,$E5<>$E4),ROUND(F5-F4,3),""))</TD></TR><TR><TD>I5</TD><TD>=IF(AND($A5<>$A4,$B5<>$B4),"",IF(AND($D5=$D4,$E5<>$E4),ROUND(G5-G4,2),""))</TD></TR><TR><TD>H6</TD><TD>=IF(AND($A6<>$A5,$B6<>$B5),"",IF(AND($D6=$D5,$E6<>$E5),ROUND(F6-F5,3),""))</TD></TR><TR><TD>I6</TD><TD>=IF(AND($A6<>$A5,$B6<>$B5),"",IF(AND($D6=$D5,$E6<>$E5),ROUND(G6-G5,2),""))</TD></TR><TR><TD>H7</TD><TD>=IF(AND($A7<>$A6,$B7<>$B6),"",IF(AND($D7=$D6,$E7<>$E6),ROUND(F7-F6,3),""))</TD></TR><TR><TD>I7</TD><TD>=IF(AND($A7<>$A6,$B7<>$B6),"",IF(AND($D7=$D6,$E7<>$E6),ROUND(G7-G6,2),""))</TD></TR><TR><TD>H20</TD><TD>=IF(AND($A20<>$A19,$B20<>$B19),"",IF(AND($D20=$D19,$E20<>$E19),ROUND(F20-F19,3),""))</TD></TR><TR><TD>I20</TD><TD>=IF(AND($A20<>$A19,$B20<>$B19),"",IF(AND($D20=$D19,$E20<>$E19),ROUND(G20-G19,2),""))</TD></TR><TR><TD>H21</TD><TD>=IF(AND($A21<>$A20,$B21<>$B20),"",IF(AND($D21=$D20,$E21<>$E20),ROUND(F21-F20,3),""))</TD></TR><TR><TD>I21</TD><TD>=IF(AND($A21<>$A20,$B21<>$B20),"",IF(AND($D21=$D20,$E21<>$E20),ROUND(G21-G20,2),""))</TD></TR><TR><TD>H26</TD><TD>=IF(AND($A26<>$A25,$B26<>$B25),"",IF(AND($D26=$D25,$E26<>$E25),ROUND(F26-F25,3),""))</TD></TR><TR><TD>I26</TD><TD>=IF(AND($A26<>$A25,$B26<>$B25),"",IF(AND($D26=$D25,$E26<>$E25),ROUND(G26-G25,2),""))</TD></TR><TR><TD>H57</TD><TD>=IF(AND($A57<>$A56,$B57<>$B56),"",IF(AND($D57=$D56,$E57<>$E56),ROUND(F57-F56,3),""))</TD></TR><TR><TD>I57</TD><TD>=IF(AND($A57<>$A56,$B57<>$B56),"",IF(AND($D57=$D56,$E57<>$E56),ROUND(G57-G56,2),""))</TD></TR><TR><TD>H58</TD><TD>=IF(AND($A58<>$A57,$B58<>$B57),"",IF(AND($D58=$D57,$E58<>$E57),ROUND(F58-F57,3),""))</TD></TR><TR><TD>I58</TD><TD>=IF(AND($A58<>$A57,$B58<>$B57),"",IF(AND($D58=$D57,$E58<>$E57),ROUND(G58-G57,2),""))</TD></TR><TR><TD>H63</TD><TD>=IF(AND($A63<>$A62,$B63<>$B62),"",IF(AND($D63=$D62,$E63<>$E62),ROUND(F63-F62,3),""))</TD></TR><TR><TD>I63</TD><TD>=IF(AND($A63<>$A62,$B63<>$B62),"",IF(AND($D63=$D62,$E63<>$E62),ROUND(G63-G62,2),""))</TD></TR><TR><TD>H64</TD><TD>=IF(AND($A64<>$A63,$B64<>$B63),"",IF(AND($D64=$D63,$E64<>$E63),ROUND(F64-F63,3),""))</TD></TR><TR><TD>I64</TD><TD>=IF(AND($A64<>$A63,$B64<>$B63),"",IF(AND($D64=$D63,$E64<>$E63),ROUND(G64-G63,2),""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Excel 2010 - I have two data sources that I need to compare (50,000 lines in one & 150,000 in the other), they are both in pivot tables with the same field headings. I need to compare the data from both sources and end up with a variance report. I read somewhere it worked better to put them into one table. So I did that but I can't get the formula right.
=IF(AND($A4<>$A3,$B4<>$B3),"",IF(AND($D4=$D3,$E4<>$E3),ROUND(F4-F3,3),"")) - This formula above is in column H & copied to I.
If the client and product are the same I need to compare the acquisition date from the both, if that's the same, I need the variance of the quantity & total cost. This seems to work fine if there is an external & internal source for the same acquisition date for the same client. However, if there is only one line item for the client or if there is either only an internal or external data source for a particular product it's not picking it up. I'm sure it's because I'm excluding any that don't have the same client in the first formula but I'm not sure how else to do it. I've tried many variations & still end up with a variance report that's not quite complete. The items in yellow aren't working correctly.
I've searched for this but almost all are only comparing 1-2 columns & not calculating anything on the data.
Would it be better to compare pivot tables or do this entirely different?
Once I have the original pass on this & all variances are dealt with, I will need to run this on a weekly basis at a minimum but preferably daily.
Again, I appreciate any help.
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 118px"><COL style="WIDTH: 90px"><COL style="WIDTH: 325px"><COL style="WIDTH: 96px"><COL style="WIDTH: 64px"><COL style="WIDTH: 84px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Client/Account</TD><TD>Product #</TD><TD style="BACKGROUND-COLOR: #ccffcc">Product</TD><TD>ACQ DATE</TD><TD>Source</TD><TD>QTY.</TD><TD>TOTAL COST.</TD><TD style="BACKGROUND-COLOR: #ccffcc">Quantity Variance</TD><TD style="BACKGROUND-COLOR: #ccffcc">Cost Variance</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">A</TD><TD style="BACKGROUND-COLOR: #ffff00">IBM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1/17/2012</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">13,300.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">134,483.00</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">3/16/2011</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">350.000</TD><TD style="TEXT-ALIGN: right">21,300.13</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">3/16/2011</TD><TD>External</TD><TD style="TEXT-ALIGN: right">22.000</TD><TD style="TEXT-ALIGN: right">1,338.87</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">(328.000)</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">(19,961.260)</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">5/4/2011</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">165.000</TD><TD style="TEXT-ALIGN: right">10,989.99</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">5/4/2011</TD><TD>External</TD><TD style="TEXT-ALIGN: right">165.000</TD><TD style="TEXT-ALIGN: right">10,989.99</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">B</TD><TD style="BACKGROUND-COLOR: #ffff00">APPLE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6/2/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">160.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">10,914.80</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1/12/1970</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">462.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8,195.88</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1/3/1979</TD><TD style="BACKGROUND-COLOR: #ffff00">Internal</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">393.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6,630.38</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 1</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">12/31/2010</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3,338.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.00</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">57</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 2</TD><TD style="BACKGROUND-COLOR: #ffff00">D</TD><TD style="BACKGROUND-COLOR: #ffff00">BHP</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5/3/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Internal</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3,023.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">253,468.88</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">58</TD><TD style="BACKGROUND-COLOR: #ffff00">Client 2</TD><TD style="BACKGROUND-COLOR: #ffff00">E</TD><TD style="BACKGROUND-COLOR: #ffff00">MICROSOFT</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2/8/1993</TD><TD style="BACKGROUND-COLOR: #ffff00">External</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">21.000</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5.12</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">63</TD><TD>Client 2</TD><TD>E</TD><TD style="BACKGROUND-COLOR: #ccffcc">MICROSOFT</TD><TD style="TEXT-ALIGN: right">9/1/2010</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">3,248.000</TD><TD style="TEXT-ALIGN: right">50,473.92</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">64</TD><TD>Client 2</TD><TD>E</TD><TD style="BACKGROUND-COLOR: #ccffcc">MICROSOFT</TD><TD style="TEXT-ALIGN: right">9/1/2010</TD><TD>External</TD><TD style="TEXT-ALIGN: right">3,248.000</TD><TD style="TEXT-ALIGN: right">50,473.92</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.000 </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H2</TD><TD>=IF(AND($A2<>$A1,$B2<>$B1),"",IF(AND($D2=$D1,$E2<>$E1),ROUND(F2-F1,3),""))</TD></TR><TR><TD>I2</TD><TD>=IF(AND($A2<>$A1,$B2<>$B1),"",IF(AND($D2=$D1,$E2<>$E1),ROUND(G2-G1,2),""))</TD></TR><TR><TD>H3</TD><TD>=IF(AND($A3<>$A2,$B3<>$B2),"",IF(AND($D3=$D2,$E3<>$E2),ROUND(F3-F2,3),""))</TD></TR><TR><TD>I3</TD><TD>=IF(AND($A3<>$A2,$B3<>$B2),"",IF(AND($D3=$D2,$E3<>$E2),ROUND(G3-G2,2),""))</TD></TR><TR><TD>H4</TD><TD>=IF(AND($A4<>$A3,$B4<>$B3),"",IF(AND($D4=$D3,$E4<>$E3),ROUND(F4-F3,3),""))</TD></TR><TR><TD>I4</TD><TD>=IF(AND($A4<>$A3,$B4<>$B3),"",IF(AND($D4=$D3,$E4<>$E3),ROUND(G4-G3,2),""))</TD></TR><TR><TD>H5</TD><TD>=IF(AND($A5<>$A4,$B5<>$B4),"",IF(AND($D5=$D4,$E5<>$E4),ROUND(F5-F4,3),""))</TD></TR><TR><TD>I5</TD><TD>=IF(AND($A5<>$A4,$B5<>$B4),"",IF(AND($D5=$D4,$E5<>$E4),ROUND(G5-G4,2),""))</TD></TR><TR><TD>H6</TD><TD>=IF(AND($A6<>$A5,$B6<>$B5),"",IF(AND($D6=$D5,$E6<>$E5),ROUND(F6-F5,3),""))</TD></TR><TR><TD>I6</TD><TD>=IF(AND($A6<>$A5,$B6<>$B5),"",IF(AND($D6=$D5,$E6<>$E5),ROUND(G6-G5,2),""))</TD></TR><TR><TD>H7</TD><TD>=IF(AND($A7<>$A6,$B7<>$B6),"",IF(AND($D7=$D6,$E7<>$E6),ROUND(F7-F6,3),""))</TD></TR><TR><TD>I7</TD><TD>=IF(AND($A7<>$A6,$B7<>$B6),"",IF(AND($D7=$D6,$E7<>$E6),ROUND(G7-G6,2),""))</TD></TR><TR><TD>H20</TD><TD>=IF(AND($A20<>$A19,$B20<>$B19),"",IF(AND($D20=$D19,$E20<>$E19),ROUND(F20-F19,3),""))</TD></TR><TR><TD>I20</TD><TD>=IF(AND($A20<>$A19,$B20<>$B19),"",IF(AND($D20=$D19,$E20<>$E19),ROUND(G20-G19,2),""))</TD></TR><TR><TD>H21</TD><TD>=IF(AND($A21<>$A20,$B21<>$B20),"",IF(AND($D21=$D20,$E21<>$E20),ROUND(F21-F20,3),""))</TD></TR><TR><TD>I21</TD><TD>=IF(AND($A21<>$A20,$B21<>$B20),"",IF(AND($D21=$D20,$E21<>$E20),ROUND(G21-G20,2),""))</TD></TR><TR><TD>H26</TD><TD>=IF(AND($A26<>$A25,$B26<>$B25),"",IF(AND($D26=$D25,$E26<>$E25),ROUND(F26-F25,3),""))</TD></TR><TR><TD>I26</TD><TD>=IF(AND($A26<>$A25,$B26<>$B25),"",IF(AND($D26=$D25,$E26<>$E25),ROUND(G26-G25,2),""))</TD></TR><TR><TD>H57</TD><TD>=IF(AND($A57<>$A56,$B57<>$B56),"",IF(AND($D57=$D56,$E57<>$E56),ROUND(F57-F56,3),""))</TD></TR><TR><TD>I57</TD><TD>=IF(AND($A57<>$A56,$B57<>$B56),"",IF(AND($D57=$D56,$E57<>$E56),ROUND(G57-G56,2),""))</TD></TR><TR><TD>H58</TD><TD>=IF(AND($A58<>$A57,$B58<>$B57),"",IF(AND($D58=$D57,$E58<>$E57),ROUND(F58-F57,3),""))</TD></TR><TR><TD>I58</TD><TD>=IF(AND($A58<>$A57,$B58<>$B57),"",IF(AND($D58=$D57,$E58<>$E57),ROUND(G58-G57,2),""))</TD></TR><TR><TD>H63</TD><TD>=IF(AND($A63<>$A62,$B63<>$B62),"",IF(AND($D63=$D62,$E63<>$E62),ROUND(F63-F62,3),""))</TD></TR><TR><TD>I63</TD><TD>=IF(AND($A63<>$A62,$B63<>$B62),"",IF(AND($D63=$D62,$E63<>$E62),ROUND(G63-G62,2),""))</TD></TR><TR><TD>H64</TD><TD>=IF(AND($A64<>$A63,$B64<>$B63),"",IF(AND($D64=$D63,$E64<>$E63),ROUND(F64-F63,3),""))</TD></TR><TR><TD>I64</TD><TD>=IF(AND($A64<>$A63,$B64<>$B63),"",IF(AND($D64=$D63,$E64<>$E63),ROUND(G64-G63,2),""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Last edited: