Match Columns in Two sheets

[Masam]

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 (below please find the formula). 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. :wink:

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>

=SUMPRODUCT([COLOR=blue]--([COLOR=red]Sheet2!$B$1:$E$1<=B2[/COLOR]),--([COLOR=red]Sheet2!$B$1:$E$1>=C2[/COLOR]),([COLOR=red]Sheet2!B2:E2[/COLOR])[/COLOR])

The above code is working perfectly fine only need that ID on next sheet should be anywhere (unsorted) it will match the ID and show the column. I have used another formula for matching but still facing same problem its not properly matching.

=IF(VLOOKUP(A2,Sheet2!A2:R25,1,TRUE), SUMPRODUCT(--(Sheet2!$B$1:$R$1<=B2),--(Sheet2!$B$1:$R$1>=C2),(Sheet2!B2:R2)))

Best Regards
Masam