how to rank top 5, then copy into new column

[nurulhidayah]

Hi,

Please help me how to top 5 this data, and then copy into new column.

A.) This is main data.
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256 x:str><COLGROUP><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 5888" width=184><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 138pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=19 width=184>[FONT=ＭＳ Ｐゴシック]Incident Area[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=72 x:str="'Count">[FONT=ＭＳ Ｐゴシック]Count[/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=19 x:str="'other">[FONT=ＭＳ Ｐゴシック]other[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]1[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'high place">[FONT=ＭＳ Ｐゴシック]high place[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]2[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'low place">[FONT=ＭＳ Ｐゴシック]low place[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]2[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'closed area">[FONT=ＭＳ Ｐゴシック]closed area[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]0[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'passageway">[FONT=ＭＳ Ｐゴシック]passageway[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]0[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'road">[FONT=ＭＳ Ｐゴシック]road[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]4[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'machine operating area">[FONT=ＭＳ Ｐゴシック]machine operating area[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]3[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'high temperature">[FONT=ＭＳ Ｐゴシック]high temperature[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]8[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'low temperature">[FONT=ＭＳ Ｐゴシック]low temperature[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]6[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18 x:str="'noise/vibration">[FONT=ＭＳ Ｐゴシック]noise/vibration[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 align=right x:num>[FONT=ＭＳ Ｐゴシック]2[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=18 x:str="'dust">[FONT=ＭＳ Ｐゴシック]dust[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>[FONT=ＭＳ Ｐゴシック]1[/FONT]</TD></TR></TBODY></TABLE>

B.) After top 5, and the rest of area (not in top 5) will be accumulated into ' Others' as below.

<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=240 x:str><COLGROUP><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 5376" width=168><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 126pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=19 width=168>[FONT=ＭＳ Ｐゴシック]Incident Area[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=72 x:str="'Count">[FONT=ＭＳ Ｐゴシック]Count[/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=19 x:str="'high temperature">[FONT=ＭＳ Ｐゴシック]high temperature[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>[FONT=ＭＳ Ｐゴシック]8[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=18 x:str="'low temperature">[FONT=ＭＳ Ｐゴシック]low temperature[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>[FONT=ＭＳ Ｐゴシック]6[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=18 x:str="'road">[FONT=ＭＳ Ｐゴシック]road[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>[FONT=ＭＳ Ｐゴシック]4[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=18 x:str="'machine operating area">[FONT=ＭＳ Ｐゴシック]machine operating area[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>[FONT=ＭＳ Ｐゴシック]3[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=18 x:str="'high place">[FONT=ＭＳ Ｐゴシック]high place[/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num>[FONT=ＭＳ Ｐゴシック]2[/FONT]</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=18>[FONT=ＭＳ Ｐゴシック]Others[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 align=right x:num>[FONT=ＭＳ Ｐゴシック]6[/FONT]</TD></TR></TBODY></TABLE>

Note : I also have difficulties when the the incident area have same value.

I hope someone can solve my problem. Thanks in advance.

 

[jasonb75]

Welcome to the board!

Maybe not the best way, but it works, changed count of low temperature to show formula working with duplicates.

Sheet1

<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: 144px"><COL style="WIDTH: 41px"><COL style="WIDTH: 176px"><COL style="WIDTH: 144px"><COL style="WIDTH: 60px"></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></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">Incident Area</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">Count</TD><TD>Helper Column</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">Incident Area</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">Count</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">other</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">1</TD><TD style="TEXT-ALIGN: right">100010002</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">low temperature</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">high place</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">2</TD><TD style="TEXT-ALIGN: right">100020003</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">high temperature</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">low place</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">2</TD><TD style="TEXT-ALIGN: right">100020004</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">road</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">closed area</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">0</TD><TD style="TEXT-ALIGN: right">100000005</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">machine operating area</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">passageway</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">0</TD><TD style="TEXT-ALIGN: right">100000006</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">noise/vibration</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">road</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: right">100040007</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">Other</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">machine operating area</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right">100030008</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">high temperature</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">8</TD><TD style="TEXT-ALIGN: right">100080009</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">low temperature</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">8</TD><TD style="TEXT-ALIGN: right">100080010</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">noise/vibration</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">2</TD><TD style="TEXT-ALIGN: right">100020011</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-FAMILY: MS P????; FONT-SIZE: 10pt">dust</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: MS P????; FONT-SIZE: 10pt">1</TD><TD style="TEXT-ALIGN: right">100010012</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; 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>C2</TD><TD>=VALUE(1&TEXT(B2,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>D2</TD><TD>=INDEX($A$2:$A$12,MATCH(LARGE($C$2:$C$12,ROW(A1)),$C$2:$C$12,0),1)</TD></TR><TR><TD>E2</TD><TD>=VLOOKUP(D2,$A$2:$B$12,2,0)</TD></TR><TR><TD>C3</TD><TD>=VALUE(1&TEXT(B3,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>D3</TD><TD>=INDEX($A$2:$A$12,MATCH(LARGE($C$2:$C$12,ROW(A2)),$C$2:$C$12,0),1)</TD></TR><TR><TD>E3</TD><TD>=VLOOKUP(D3,$A$2:$B$12,2,0)</TD></TR><TR><TD>C4</TD><TD>=VALUE(1&TEXT(B4,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>D4</TD><TD>=INDEX($A$2:$A$12,MATCH(LARGE($C$2:$C$12,ROW(A3)),$C$2:$C$12,0),1)</TD></TR><TR><TD>E4</TD><TD>=VLOOKUP(D4,$A$2:$B$12,2,0)</TD></TR><TR><TD>C5</TD><TD>=VALUE(1&TEXT(B5,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>D5</TD><TD>=INDEX($A$2:$A$12,MATCH(LARGE($C$2:$C$12,ROW(A4)),$C$2:$C$12,0),1)</TD></TR><TR><TD>E5</TD><TD>=VLOOKUP(D5,$A$2:$B$12,2,0)</TD></TR><TR><TD>C6</TD><TD>=VALUE(1&TEXT(B6,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>D6</TD><TD>=INDEX($A$2:$A$12,MATCH(LARGE($C$2:$C$12,ROW(A5)),$C$2:$C$12,0),1)</TD></TR><TR><TD>E6</TD><TD>=VLOOKUP(D6,$A$2:$B$12,2,0)</TD></TR><TR><TD>C7</TD><TD>=VALUE(1&TEXT(B7,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>E7</TD><TD>=SUM(B2:B12)-SUM(E2:E6)</TD></TR><TR><TD>C8</TD><TD>=VALUE(1&TEXT(B8,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>C9</TD><TD>=VALUE(1&TEXT(B9,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>C10</TD><TD>=VALUE(1&TEXT(B10,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>C11</TD><TD>=VALUE(1&TEXT(B11,"0000")&TEXT(ROW(),"0000"))</TD></TR><TR><TD>C12</TD><TD>=VALUE(1&TEXT(B12,"0000")&TEXT(ROW(),"0000"))</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


Hope this helps.

 

[nurulhidayah]

Thanks jason, those formula are really helpful..