Formula to compare 4 fields & calculate variance

D

dconnors

New Member
Joined
Nov 10, 2009
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Could you clarify a few things please:

1. Rows in your sample data are skipped. Since row 8 doesn't display, should I assume data on row 7 does not match row 8? Same for row 26.

2. What results do you want in the variance columns if only the dates don't match, as in row 20 & 21? Are these assumed to be non-matching rows or are they matching rows with date discrepancies? How can you distinguish between the 2.

3. What results do you want in the variance columns if there is no matching row, as in row 1, 7, 26, 57, and 58?

4. Is your data sorted by Customer, Product#, Source, Acq Date in that order?

5. Does the 150,000 database contain all the records of the 50,000 database?
 
Upvote 0
1. I hid much of the data that actually matched. I'm still not sure how much data to actually attach when requesting help :( Here's the data around 7 & 26.


<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: 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="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">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">8</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">7/5/2011</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">160.000</TD><TD style="TEXT-ALIGN: right">10,471.60</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">9</TD><TD>Client 1</TD><TD>B</TD><TD style="BACKGROUND-COLOR: #ccffcc">APPLE</TD><TD style="TEXT-ALIGN: right">7/5/2011</TD><TD>External</TD><TD style="TEXT-ALIGN: right">160.000</TD><TD style="TEXT-ALIGN: right">10,471.60</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR></TBODY></TABLE>


<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: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Client 1</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">14,176.000</TD><TD style="TEXT-ALIGN: right">220,295.04</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">25</TD><TD>Client 1</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">10,769.000</TD><TD style="TEXT-ALIGN: right">167,350.26</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">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">27</TD><TD>Client 2</TD><TD>D</TD><TD style="BACKGROUND-COLOR: #ccffcc">BHP</TD><TD style="TEXT-ALIGN: right">1/16/2002</TD><TD>Internal</TD><TD style="TEXT-ALIGN: right">128,015.000</TD><TD style="TEXT-ALIGN: right">0.00</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">28</TD><TD>Client 2</TD><TD>D</TD><TD style="BACKGROUND-COLOR: #ccffcc">BHP</TD><TD style="TEXT-ALIGN: right">1/16/2002</TD><TD>External</TD><TD style="TEXT-ALIGN: right">131,038.000</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR></TBODY></TABLE>
2. If only the dates don't match, these should be variances in the data...in the end the data in the two systems should match. Obviously since there are quite a few lines less in one than the other we've alot of work yet to do. If there isn't matching data in each database for same client, product, & acquistion dates, then the variance should be the full amount of quantity & cost...column H should equal F and I should equal G.

3. If no matching row, H should equal F & I should equal G since I need all variances between the two sources.

4. When I get the data its not sorted at all. However I have been sorting it by Client/Account, Product #, Acquistion date, in that order.

5. The 150,000 database doesn't necessary contain all data in the 50,000 database. There could be data in each that is not in the other.

Hope that answers your questions. I appreciate you taking a look at this.
 
Last edited:
Upvote 0
I think I understand now. Try this in H2 and copy down and across to end of data in Col I:

=IF(AND($A2=$A3,$B2=$B3,$D2=$D3),IF($E2=$E3,$F2,"see below"),IF(AND($A2=$A1,$B2=$B1,$D2=$D1),ROUND(F2-F1,3),F2))
 
Upvote 0
It works really well, thanks again!!! probably saved me another 12 hours :biggrin:
 
Upvote 0
I did end up tweaking the formula so the external source would populate with a positive variance if there were no matches & the internal with a negative variance, so the results are more consistent and it's easier to compare the data instead of all variances being positive. So far it looks like it worked.

This is the formula in cell H7.

=IF(AND($A7=$A8,$B7=$B8,$D7=$D8),IF($E7=$E8,IF($E7="External",F7,F7*-1),""),IF(AND($A7=$A6,$B7=$B6,$D7=$D6),ROUND(F7-F6,3),IF($E7="Interal",-1*F7,F7)))
 
Upvote 0
You could shorten:
F7*-1
to
-F7

This seems to give the same results as your revised formula:
=IF(AND($A2=$A3,$B2=$B3,$D2=$D3),IF($E2=$E3,$F2,""),IF(AND($A2=$A1,$B2=$B1,$D2=$D1),ROUND(F2-F1,3),F2))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
348
esben
E
V
  • Question
Issue with scraping with VBA
Replies
1
Views
506
John_w
J
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A

Forum statistics

Threads
1,214,914
Messages
6,122,211
Members
449,074
Latest member
cancansova

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top