Needs Excel Macro

[Masam]

Hello Everyone,

Just facing the problem. I m getting data on daily basis against the ID in the form of of excel spreadsheet. I need Row Sum between a user specified column. so i m looking for an macro that it will automatically run, takes the input from the user for the specified 2 columns and shows the sum of between two columns.

Actually I have two Sheets in a Excel file.

Sheet 1:

ID    MaxDate        Last Date    TOTALSUM
1543    04/03/2011    02/03/2011    
1544    04/03/2011    01/03/2011

Sheet 2:

ID        1-Mar-11    2-Mar-11    3-Mar-11    4-Mar-11
1543        21        7.6        8.2        9.1
1544        11        2.6        4.5        10.0

Written Code on excel VBA as it needs to be more refine but i m stuck

Sub Combined()
A = 1
B = 1

For I = 0 To 4
For j = 0 To 4
If Sheet1.Range("A1").Offset(I, 0) = Sheet2.Range("A1").Offset(j + 1, 0) Then
    If Sheet1.Range("A1").Offset(I, 1) = Sheet2.Range("A1").Offset(0, j + 1) Then
        Do
          e = Sheet2.Range("A1").Offset(A, B) + Sheet2.Range("A1").Offset(A + j + 1, B + j + 1)
          Loop Until Sheet1.Range("A1").Offset(d, 0) <> Sheet2.Range("A1").Offset(f, 0)
          Sheet1.Range("A1").Offset(1, 3) = e
    Else
         B = B + 1
    End If
Else
    A = A + 1
End If
Next j
Next I
Sheet1.Range("A1").Offset(1, 3) = e

End Sub

BELOW The result which is actually i need:

ID    MaxDate        Last Date    TOTALSUM
1543    04/03/2011    03/03/2011    17.3        
1544    04/03/2011    01/03/2011    28.1

BestRegards
Masam

 

[gsistek]

I think it might be easier without a macro. I just used a sumproduct formula andit worked just fine.
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>ID</TD><TD>MaxDate</TD><TD>Last Date</TD><TD>TOTALSUM</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1543</TD><TD style="TEXT-ALIGN: right">4/3/2011</TD><TD style="TEXT-ALIGN: right">3/3/2011</TD><TD style="TEXT-ALIGN: right">17.3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">1544</TD><TD style="TEXT-ALIGN: right">4/3/2011</TD><TD style="TEXT-ALIGN: right">1/3/2011</TD><TD style="TEXT-ALIGN: right">28.1</TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(Sheet2!$B$1:$E$1<=B2),--(Sheet2!$B$1:$E$1>=C2),(Sheet2!B2:E2))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(--(Sheet2!$B$1:$E$1<=B3),--(Sheet2!$B$1:$E$1>=C3),(Sheet2!B3:E3))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>ID</TD><TD style="TEXT-ALIGN: right">1-Mar-11</TD><TD style="TEXT-ALIGN: right">2-Mar-11</TD><TD style="TEXT-ALIGN: right">3-Mar-11</TD><TD style="TEXT-ALIGN: right">4-Mar-11</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1543</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">7.6</TD><TD style="TEXT-ALIGN: right">8.2</TD><TD style="TEXT-ALIGN: right">9.1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">1544</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">2.6</TD><TD style="TEXT-ALIGN: right">4.5</TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

 

[Masam]

Sorry late getting back to you. Thanks so much for such a perfect solution. I m getting good results.
One problem more that is if Sheet2 has different sorting like the ID column is not sorted properly but Sheet1 has sorted ID's it will show the wrong result so is that possible I can match the both ID columns in Sheet1 and Sheet2 and it will automatically check match the ID columns and then run the SUMPRODUCT formula which you have mentioned. Because if the ID column in Sheet2 is not sorted it will give me the wrong results as I have already checked. Or is there any kind of possibility I can build macro and use button for SORT and it will automatically sort sheet2 ID column. I hope you can understand, please if you have any questions let me know. But a big thanks again.

Note: matching ID column is important for me because total ID's in a sheet = 3000 to 3500 ( records).

Example:

Excel 2007<table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><colgroup><col style="BACKGROUND-COLOR: #e0e0f0" width="25"><col><col><col><col></colgroup><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="TEXT-ALIGN: center; COLOR: #161120">1</td><td>ID</td><td>MaxDate</td><td>Last Date</td><td>TOTALSUM</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">2</td><td style="TEXT-ALIGN: right">1543</td><td style="TEXT-ALIGN: right">4/3/2011</td><td style="TEXT-ALIGN: right">3/3/2011</td><td style="TEXT-ALIGN: right">17.3</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">3</td><td style="TEXT-ALIGN: right">1544</td><td style="TEXT-ALIGN: right">4/3/2011</td><td style="TEXT-ALIGN: right">1/3/2011</td><td style="TEXT-ALIGN: right">28.1</td></tr></tbody></table>Sheet1


Excel 2007<table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><colgroup><col style="BACKGROUND-COLOR: #e0e0f0" width="25"><col><col><col><col><col></colgroup><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="TEXT-ALIGN: center; COLOR: #161120">1</td><td>ID</td><td style="TEXT-ALIGN: right">1-Mar-11</td><td style="TEXT-ALIGN: right">2-Mar-11</td><td style="TEXT-ALIGN: right">3-Mar-11</td><td style="TEXT-ALIGN: right">4-Mar-11</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">2</td><td style="TEXT-ALIGN: right">1544</td><td style="TEXT-ALIGN: right">21</td><td style="TEXT-ALIGN: right">7.6</td><td style="TEXT-ALIGN: right">8.2</td><td style="TEXT-ALIGN: right">9.1</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">3</td><td style="TEXT-ALIGN: right">1543</td><td style="TEXT-ALIGN: right">11</td><td style="TEXT-ALIGN: right">2.6</td><td style="TEXT-ALIGN: right">4.5</td><td style="TEXT-ALIGN: right">10</td></tr></tbody></table><table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>Sheet2</th></tr></thead><tbody></tbody></table>



Best Regards
Masam

 

[gsistek]

Use this formula instead:

=SUMPRODUCT(--(Sheet2!$B$1:$E$1<=B2),--(Sheet2!$B$1:$E$1>=C2),(INDEX(Sheet2!B:E,MATCH(A2,Sheet2!A:A,0),0)))