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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top