how to rank top 5, then copy into new column

nurulhidayah

New Member
Joined
Jun 17, 2010
Messages
5
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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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=MS Pゴシック]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

Well-known Member
Joined
Dec 30, 2008
Messages
7,392
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,085,495
Messages
5,384,004
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top