Macro or formula to transpose data

[Devil]

I have some csv files in the following format, Sheet 1
<TABLE style="WIDTH: 280pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=372 border=0 x:str><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 129pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=172 height=17>Folder Path</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=114> Zip File</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86> Pages Count</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\01</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\02</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\03</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>24</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\04</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>44</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\05</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\22</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\23</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\24</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>26</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\25</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>48</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\26</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>24</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\27</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>42</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\ABCD\2011\03\28</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ABCD : 12</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> Total Pages : 330</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\XYZ\2011\03\01</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\XYZ\2011\03\02</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>36</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\XYZ\2011\03\03</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>34</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\XYZ\2011\03\25</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>58</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\XYZ\2011\03\26</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>60</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\XYZ\2011\03\27</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>92</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C:\Check\XYZ\2011\03\28</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">XYZ</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>26</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>XYZ : 7</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> Total Pages : 336</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

I need this formatted in the below format, Sheet 2
<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=704 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=64 height=21>ZIP NAME</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40603">01-Mar</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40604">02-Mar</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40605">03-Mar</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40606">04-Mar</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40607">05-Mar</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40608">06-Mar</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40609">07-Mar</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40610">08-Mar</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40611">09-Mar</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40612">10-Mar</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ABCD</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>20</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>24</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>44</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>22</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>XYZ</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>30</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>36</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>34</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Sum</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(B2:B3)">50</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(C2:C3)">56</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(D2:D3)">58</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(E2:E3)">44</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(F2:F3)">22</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(G2:G3)">0</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(H2:H3)">0</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(I2:I3)">0</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(J2:J3)">0</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=SUM(K2:K3)">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Count</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(B2:B3)-COUNTIF(B2:B3,0)">2</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(C2:C3)-COUNTIF(C2:C3,0)">2</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(D2:D3)-COUNTIF(D2:D3,0)">2</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(E2:E3)-COUNTIF(E2:E3,0)">1</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(F2:F3)-COUNTIF(F2:F3,0)">1</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(G2:G3)-COUNTIF(G2:G3,0)">0</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(H2:H3)-COUNTIF(H2:H3,0)">0</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(I2:I3)-COUNTIF(I2:I3,0)">0</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(J2:J3)-COUNTIF(J2:J3,0)">0</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" align=right x:num x:fmla="=COUNT(K2:K3)-COUNTIF(K2:K3,0)">0</TD></TR></TBODY></TABLE>

Sheet 2 should display 0 if there are no entries in Sheet 1 against the particular date.

I used to copy and transpose the data till date, however now the data in sheet 1 has increased to a substantial number and am putting in lots of time and effort to get it into Sheet 2.

Any macro or formula to achieve this will help me a lot.

Thanks in advance.

 

[Haseeb Avarakkan]

Hi,

What version of excel are you using?

 

[Haseeb Avarakkan]

Hi,

If you have Excel 2003 or earlier try this,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ZIP NAME</td><td style="text-align: right;;">1-Mar</td><td style="text-align: right;;">2-Mar</td><td style="text-align: right;;">3-Mar</td><td style="text-align: right;;">4-Mar</td><td style="text-align: right;;">5-Mar</td><td style="text-align: right;;">6-Mar</td><td style="text-align: right;;">7-Mar</td><td style="text-align: right;;">8-Mar</td><td style="text-align: right;;">9-Mar</td><td style="text-align: right;;">10-Mar</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABCD</td><td style="text-align: right;;">20</td><td style="text-align: right;;">20</td><td style="text-align: right;;">24</td><td style="text-align: right;;">44</td><td style="text-align: right;;">22</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">XYZ</td><td style="text-align: right;;">30</td><td style="text-align: right;;">36</td><td style="text-align: right;;">34</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Sum</td><td style="text-align: right;;">50</td><td style="text-align: right;;">56</td><td style="text-align: right;;">58</td><td style="text-align: right;;">44</td><td style="text-align: right;;">22</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Count</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">Sheet1!$B$2:$B$22=$A2</font>),--(<font color="Red">SUBSTITUTE(<font color="Green">RIGHT(<font color="Purple">Sheet1!$A$2:$A$22,10</font>),"\","/"</font>)=TEXT(<font color="Green">B$1,"yyyy/mm/dd"</font>)</font>),Sheet1!$C$2:$C$22</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">Sheet1!$B$2:$B$22=$A3</font>),--(<font color="Red">SUBSTITUTE(<font color="Green">RIGHT(<font color="Purple">Sheet1!$A$2:$A$22,10</font>),"\","/"</font>)=TEXT(<font color="Green">B$1,"yyyy/mm/dd"</font>)</font>),Sheet1!$C$2:$C$22</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=SUM(<font color="Blue">B2:B3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=COUNTIF(<font color="Blue">Sheet1!$A$2:$A$22,"*"&SUBSTITUTE(<font color="Red">TEXT(<font color="Green">Sheet2!B$1,"yyyy/mm/dd"</font>),"/","\"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
If you have Excel 2007 or later try this,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ZIP NAME</td><td style="text-align: right;;">1-Mar</td><td style="text-align: right;;">2-Mar</td><td style="text-align: right;;">3-Mar</td><td style="text-align: right;;">4-Mar</td><td style="text-align: right;;">5-Mar</td><td style="text-align: right;;">6-Mar</td><td style="text-align: right;;">7-Mar</td><td style="text-align: right;;">8-Mar</td><td style="text-align: right;;">9-Mar</td><td style="text-align: right;;">10-Mar</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABCD</td><td style="text-align: right;;">20</td><td style="text-align: right;;">20</td><td style="text-align: right;;">24</td><td style="text-align: right;;">44</td><td style="text-align: right;;">22</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">XYZ</td><td style="text-align: right;;">30</td><td style="text-align: right;;">36</td><td style="text-align: right;;">34</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Sum</td><td style="text-align: right;;">50</td><td style="text-align: right;;">56</td><td style="text-align: right;;">58</td><td style="text-align: right;;">44</td><td style="text-align: right;;">22</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Count</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=SUMIFS(<font color="Blue">Sheet1!$C:$C,Sheet1!$B:$B,$A2,Sheet1!$A:$A,"*"&SUBSTITUTE(<font color="Red">TEXT(<font color="Green">B$1,"yyyy/mm/dd"</font>),"/","\"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMIFS(<font color="Blue">Sheet1!$C:$C,Sheet1!$B:$B,$A3,Sheet1!$A:$A,"*"&SUBSTITUTE(<font color="Red">TEXT(<font color="Green">B$1,"yyyy/mm/dd"</font>),"/","\"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=SUM(<font color="Blue">B2:B3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=COUNTIF(<font color="Blue">Sheet1!$A$2:$A$22,"*"&SUBSTITUTE(<font color="Red">TEXT(<font color="Green">Sheet22!B$1,"yyyy/mm/dd"</font>),"/","\"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy across.

 

[Devil]

Haseeb,

Thanks for your reply, my 'Mallu' friend, much appreciated.

Am using Excel 2003.

I will check this and revert.

Regards
Devil

 

[Devil]

That was perfect Haseeb, thanks a bunch.

Never used '--' in formulas, though i understand that it provides a logical result.

A little help in understanding your formula will make my day. I don't want to roughly copy paste your formula without understanding it completely.

 

[Haseeb Avarakkan]

That was perfect Haseeb, thanks a bunch.

Never used '--' in formulas, though i understand that it provides a logical result.

A little help in understanding your formula will make my day. I don't want to roughly copy paste your formula without understanding it completely.

Glad, it works.

SUBSTITUTE(RIGHT(Sheet1!$A$2:$A$22,10),"\","/")

This will take the last 10 characters on each cell in the range A2:A22

Will get the array of

{"2011\03\01";"2011\03\02";"2011\03\03";"2011\03\04"...}

SUBSTITUTE will replace the all "\" to "/". So will get it as yyyy/mm/dd format.

{"2011/03/01";"2011/03/02";"2011/03/03";"2011/03/04"...}

TEXT(B$1,"yyyy/mm/dd")

This will give B1 date to yyyy/mm/dd format, "2011/03/01"

SUBSTITUTE(RIGHT(Sheet1!$A$2:$A$22,10),"\","/")=TEXT(B$1,"yyyy/mm/dd"))

This will give TRUE/FALSE on the each SUBSTITUTEd array, which is equal to B1 value.

{TRUE;FALSE;FALSE;FALSE;FALSE....}

--{TRUE;FALSE;FALSE;FALSE;FALSE..} will convert the TRUE/FALSE to 1/0.

Sheet1!$B$2:$B$22=$A2, here will get a TRUE/FALSE array if the range is =A2

'--' will convert them to 1/0.

Finally will get the array of

=SUMPRODUCT({1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0},{1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0},{20;20;24;44;22;20;20;26;48;24;42;20;0;30;36;34;58;60;92;26;0})

SUMPRODUCT will multiple each array then SUM them.

(1*1*20)+(1*0*20)+(1*0*24)+(1*0*44)+.....

20+0+0+0......

Please see the below link about SUMPRODUCT.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Devil]

Thanks again for the link and your detailed explanation.