filling rows and colums in order in another sheet

[rao.venkat]

Hi All
I have data like this
<table border="0" cellpadding="0" cellspacing="0" width="309"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">case no
</td> <td class="xl63" style="width:61pt" width="81"> time</td> <td style="width:48pt" width="64">status</td> <td style="width:75pt" width="100">failed type</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224510</td> <td class="xl63" align="right">13:00:00</td> <td> success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224511</td> <td class="xl63" align="right">13:10:05
</td> <td> success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224512</td> <td class="xl63" align="right">13:20:10</td> <td> success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224513</td> <td class="xl63" align="right">13:30:15</td> <td> success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224514</td> <td class="xl63" align="right">13:40:20
</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224515</td> <td class="xl63" align="right">13:50:25</td> <td>fail</td> <td>misship</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224516</td> <td class="xl63" align="right">14:00:30</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224517</td> <td class="xl63" align="right">14:10:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224518</td> <td class="xl63" align="right">14:20:40</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224519</td> <td class="xl63" align="right">14:30:45</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224520</td> <td class="xl63" align="right">14:40:50</td> <td>fail</td> <td>ukeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224521</td> <td class="xl63" align="right">14:50:55</td> <td>fail</td> <td>ukeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224522</td> <td class="xl63" align="right">15:01:00</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224523</td> <td class="xl63" align="right">15:11:05</td> <td>fail</td> <td>misship</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224524</td> <td class="xl63" align="right">15:21:10</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224525</td> <td class="xl63" align="right">15:31:15</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224526</td> <td class="xl63" align="right">15:41:20</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224527</td> <td class="xl63" align="right">15:51:25</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224528</td> <td class="xl63" align="right">16:01:30</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224529</td> <td class="xl63" align="right">16:11:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224530</td> <td class="xl63" align="right">16:21:40</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224531</td> <td class="xl63" align="right">16:31:45</td> <td>fail</td> <td>ukeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224532</td> <td class="xl63" align="right">16:41:50</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224533</td> <td class="xl63" align="right">16:51:55</td> <td>fail</td> <td>misship</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224534</td> <td class="xl63" align="right">17:02:00</td> <td>fail</td> <td>ukeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224535</td> <td class="xl63" align="right">17:12:05</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224536</td> <td class="xl63" align="right">17:22:10</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224537</td> <td class="xl63" align="right">17:32:15</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224538</td> <td class="xl63" align="right">17:42:20</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224539</td> <td class="xl63" align="right">17:52:25</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224540</td> <td class="xl63" align="right">18:02:30</td> <td>fail</td> <td>fmis</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224541</td> <td class="xl63" align="right">18:12:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224542</td> <td class="xl63" align="right">18:22:40</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224543</td> <td class="xl63" align="right">18:32:45</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224544</td> <td class="xl63" align="right">18:42:50</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224545</td> <td class="xl63" align="right">18:52:55</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224546</td> <td class="xl63" align="right">19:03:00</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224547</td> <td class="xl63" align="right">19:13:05</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224548</td> <td class="xl63" align="right">19:23:10</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224549</td> <td class="xl63" align="right">19:33:15</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224550</td> <td class="xl63" align="right">19:43:20</td> <td>fail</td> <td>ukeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224551</td> <td class="xl63" align="right">19:53:25</td> <td>fail</td> <td>misship</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224552</td> <td class="xl63" align="right">20:03:30</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224553</td> <td class="xl63" align="right">20:13:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224554</td> <td class="xl63" align="right">20:23:40</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224555</td> <td class="xl63" align="right">20:33:45</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224556</td> <td class="xl63" align="right">20:43:50</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224557</td> <td class="xl63" align="right">20:53:55</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224558</td> <td class="xl63" align="right">21:04:00</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224559</td> <td class="xl63" align="right">21:14:05</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224560</td> <td class="xl63" align="right">21:24:10</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224561</td> <td class="xl63" align="right">21:34:15</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224562</td> <td class="xl63" align="right">21:44:20</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224563</td> <td class="xl63" align="right">21:54:25</td> <td>fail</td> <td>fmis</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224564</td> <td class="xl63" align="right">22:04:30</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224565</td> <td class="xl63" align="right">22:14:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224566</td> <td class="xl63" align="right">22:24:40</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224567</td> <td class="xl63" align="right">22:34:45</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224568</td> <td class="xl63" align="right">22:44:50</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224569</td> <td class="xl63" align="right">22:54:55</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224570</td> <td class="xl63" align="right">23:05:00</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224571</td> <td class="xl63" align="right">23:15:05</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224572</td> <td class="xl63" align="right">23:25:10</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224573</td> <td class="xl63" align="right">23:35:15</td> <td>fail</td> <td>misship</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224574</td> <td class="xl63" align="right">23:45:20</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224575</td> <td class="xl63" align="right">23:55:25</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224576</td> <td class="xl63" align="right">0:05:30</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224577</td> <td class="xl63" align="right">0:15:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224578</td> <td class="xl63" align="right">0:25:40</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224579</td> <td class="xl63" align="right">0:35:45</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224580</td> <td class="xl63" align="right">0:45:50</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224581</td> <td class="xl63" align="right">0:55:55</td> <td>fail</td> <td>misship</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224582</td> <td class="xl63" align="right">1:06:00</td> <td>fail</td> <td>misship</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224583</td> <td class="xl63" align="right">1:16:05</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224584</td> <td class="xl63" align="right">1:26:10</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224585</td> <td class="xl63" align="right">1:36:15</td> <td>fail</td> <td>fmis</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224586</td> <td class="xl63" align="right">1:46:20</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224587</td> <td class="xl63" align="right">1:56:25</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224588</td> <td class="xl63" align="right">2:06:30</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224589</td> <td class="xl63" align="right">2:16:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224590</td> <td class="xl63" align="right">2:26:40</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224591</td> <td class="xl63" align="right">2:36:45</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224592</td> <td class="xl63" align="right">2:46:50</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224593</td> <td class="xl63" align="right">2:56:55</td> <td>fail</td> <td>noeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224594</td> <td class="xl63" align="right">3:07:00</td> <td>fail</td> <td>ukeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224595</td> <td class="xl63" align="right">3:17:05</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224596</td> <td class="xl63" align="right">3:27:10</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224597</td> <td class="xl63" align="right">3:37:15</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224598</td> <td class="xl63" align="right">3:47:20</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224599</td> <td class="xl63" align="right">3:57:25</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224600</td> <td class="xl63" align="right">4:07:30</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224601</td> <td class="xl63" align="right">4:17:35</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224602</td> <td class="xl63" align="right">4:27:40</td> <td>fail</td> <td>ukeqp</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224603</td> <td class="xl63" align="right">4:37:45</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224604</td> <td class="xl63" align="right">4:47:50</td> <td>fail</td> <td>ferror</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224605</td> <td class="xl63" align="right">4:57:55</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224606</td> <td class="xl63" align="right">5:08:00</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224607</td> <td class="xl63" align="right">5:18:05</td> <td>success</td> <td>blank</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">224608</td> <td class="xl63" align="right">5:28:10</td> <td>success</td> <td>blank</td> </tr> </tbody></table>
I want data like below in another sheet
<table border="0" cellpadding="0" cellspacing="0" width="883"><col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="width:48pt" span="12" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:86pt" height="20" width="115">
</td> <td class="xl66" colspan="3" style="mso-ignore:colspan;width:144pt" width="192">Failed cases Numbers</td> <td class="xl66" style="width:48pt" width="64"> </td> <td class="xl66" style="width:48pt" width="64"> </td> <td class="xl66" style="width:48pt" width="64"> </td> <td class="xl66" style="width:48pt" width="64"> </td> <td class="xl66" style="width:48pt" width="64"> </td> <td class="xl66" style="width:48pt" width="64"> </td> <td class="xl66" style="width:48pt" width="64"> </td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13:00:00-15:00:00</td> <td align="right"> 224514</td> <td align="right"> 224515</td> <td align="right"> 224516</td> <td align="right"> 224520</td> <td align="right"> 224521</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">15:00:00-18:00:00</td> <td align="right">224531</td> <td align="right">224532</td> <td align="right">224533</td> <td align="right">224534</td> <td align="right">224535</td> <td align="right"> 224531</td> <td align="right"> 224532
</td> <td align="right"> 224533</td> <td align="right"> 224534
</td> <td align="right">224535</td> <td align="right"> 224538</td> <td align="right"> 224539</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">18:00:00-21:00:00</td> <td align="right">224540</td> <td align="right">224548</td> <td align="right">224549</td> <td align="right">224550</td> <td align="right">224551</td> <td align="right">224552</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">21:00:00-00:00:00</td> <td align="right">224571</td> <td align="right">224572</td> <td align="right">224573</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">00:00:00-3:00:00</td> <td align="right">224581</td> <td align="right">224582</td> <td align="right">224583</td> <td align="right">224584</td> <td align="right">224585</td> <td align="right">224593</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[moonfish]

Is the format really important?

If not, I would recommend using a pivot table. You can sort them per hour and have them show like in this table:

Excel Workbook
	F	G
1	status	fail
2	*	*
3	hour	case no
4	0	224581
5	1	224582
6	*	224583
7	*	224584
8	*	224585
9	2	224593
10	3	224594
11	4	224602
12	*	224603
13	*	224604
14	13	224514
15	*	224515
16	14	224516
17	*	224520
18	*	224521
19	15	224522
20	*	224523
21	16	224531
22	*	224532
23	*	224533
24	17	224534
25	*	224535
26	*	224538
27	*	224539
28	18	224540
29	19	224548
30	*	224549
31	*	224550
32	*	224551
33	20	224552
34	21	224562
35	*	224563
36	23	224571
37	*	224572
38	*	224573
Sheet