Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 List
I have two columns of data A3:A72 and G3:G72. The first column has plant
names and the second column has a number of accidents. Below is a sample of the data: Plant 1-01 1 Plant 1-02 0 Plant 1-03 0 Plant 1-04 0 Plant 1-05 0 Plant 1-06 2 Plant 1-07 0 Plant 1-08 1 Plant 1-09 0 Plant 1-10 2 Plant 2-01 0 Plant 2-02 0 Plant 2-03 0 Plant 2-04 2 Plant 2-05 0 Plant 2-06 0 Plant 2-07 0 Plant 2-08 1 Plant 2-09 0 Plant 2-10 1 I would like to create a Top 10 list of the highest values in G3:G72 and then return both the Plant Name and its associated number of accidents in descending order on another tab. Several of the plants will have the same number of accidents (e.g. 0 or 1 or 2) in which case I would need all of those plants. I tried using the following formulas on the different tab but it doesn't appear to work. The first formula is to give the plant name and is started in row A1 of the tab called "Sheet1" and the second is to give the number of accidents: =INDEX('Current Year Data'!A$3:A$72,MATCH(LARGE('Current Year Data'!G$3:G$72,ROW(A1)),'Current Year Data'!G$3:G$72,0)) =INDEX('Current Year Data'!G$3:G$72,MATCH(LARGE('Current Year Data'!$G$3:$G$72,ROW(B1)),'Current Year Data'!G$3:G$72,0)) Any help wold be greatly appreciated. Jeff |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 List
Hi Jeff,
Why you don't highlight the columns and sort it by column G in that way you will have your top 10 "Jeff Gross" wrote: I have two columns of data A3:A72 and G3:G72. The first column has plant names and the second column has a number of accidents. Below is a sample of the data: Plant 1-01 1 Plant 1-02 0 Plant 1-03 0 Plant 1-04 0 Plant 1-05 0 Plant 1-06 2 Plant 1-07 0 Plant 1-08 1 Plant 1-09 0 Plant 1-10 2 Plant 2-01 0 Plant 2-02 0 Plant 2-03 0 Plant 2-04 2 Plant 2-05 0 Plant 2-06 0 Plant 2-07 0 Plant 2-08 1 Plant 2-09 0 Plant 2-10 1 I would like to create a Top 10 list of the highest values in G3:G72 and then return both the Plant Name and its associated number of accidents in descending order on another tab. Several of the plants will have the same number of accidents (e.g. 0 or 1 or 2) in which case I would need all of those plants. I tried using the following formulas on the different tab but it doesn't appear to work. The first formula is to give the plant name and is started in row A1 of the tab called "Sheet1" and the second is to give the number of accidents: =INDEX('Current Year Data'!A$3:A$72,MATCH(LARGE('Current Year Data'!G$3:G$72,ROW(A1)),'Current Year Data'!G$3:G$72,0)) =INDEX('Current Year Data'!G$3:G$72,MATCH(LARGE('Current Year Data'!$G$3:$G$72,ROW(B1)),'Current Year Data'!G$3:G$72,0)) Any help wold be greatly appreciated. Jeff |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 List
The file will be updated weekly so I was hoping to avoid back-end VBA
programming to keep the sorting up to date when the file is opened and the data is updated. Jeff "Eduardo" wrote: Hi Jeff, Why you don't highlight the columns and sort it by column G in that way you will have your top 10 "Jeff Gross" wrote: I have two columns of data A3:A72 and G3:G72. The first column has plant names and the second column has a number of accidents. Below is a sample of the data: Plant 1-01 1 Plant 1-02 0 Plant 1-03 0 Plant 1-04 0 Plant 1-05 0 Plant 1-06 2 Plant 1-07 0 Plant 1-08 1 Plant 1-09 0 Plant 1-10 2 Plant 2-01 0 Plant 2-02 0 Plant 2-03 0 Plant 2-04 2 Plant 2-05 0 Plant 2-06 0 Plant 2-07 0 Plant 2-08 1 Plant 2-09 0 Plant 2-10 1 I would like to create a Top 10 list of the highest values in G3:G72 and then return both the Plant Name and its associated number of accidents in descending order on another tab. Several of the plants will have the same number of accidents (e.g. 0 or 1 or 2) in which case I would need all of those plants. I tried using the following formulas on the different tab but it doesn't appear to work. The first formula is to give the plant name and is started in row A1 of the tab called "Sheet1" and the second is to give the number of accidents: =INDEX('Current Year Data'!A$3:A$72,MATCH(LARGE('Current Year Data'!G$3:G$72,ROW(A1)),'Current Year Data'!G$3:G$72,0)) =INDEX('Current Year Data'!G$3:G$72,MATCH(LARGE('Current Year Data'!$G$3:$G$72,ROW(B1)),'Current Year Data'!G$3:G$72,0)) Any help wold be greatly appreciated. Jeff |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 List
Jeff,
what you can do is to have a Tab called Top 10 and have a button in the other tab to execute the macro as follow Sub Top10() ' ' Top10 Macro ' ' Sheets("Top10").Select Cells.Select Selection.ClearContents Sheets("Sheet1").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Sheets("Top10").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Rows("2:2").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Worksheets("Top10").Sort.SortFields .Clear ActiveWorkbook.Worksheets("Top10").Sort.SortFields .Add Key:=Range("G2:G5"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Top10").Sort .SetRange Range("A2:H5") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A1").Select End Sub That will create a copy of you actual Data and then will sort in descending order so you will have your top 10 "Jeff Gross" wrote: The file will be updated weekly so I was hoping to avoid back-end VBA programming to keep the sorting up to date when the file is opened and the data is updated. Jeff "Eduardo" wrote: Hi Jeff, Why you don't highlight the columns and sort it by column G in that way you will have your top 10 "Jeff Gross" wrote: I have two columns of data A3:A72 and G3:G72. The first column has plant names and the second column has a number of accidents. Below is a sample of the data: Plant 1-01 1 Plant 1-02 0 Plant 1-03 0 Plant 1-04 0 Plant 1-05 0 Plant 1-06 2 Plant 1-07 0 Plant 1-08 1 Plant 1-09 0 Plant 1-10 2 Plant 2-01 0 Plant 2-02 0 Plant 2-03 0 Plant 2-04 2 Plant 2-05 0 Plant 2-06 0 Plant 2-07 0 Plant 2-08 1 Plant 2-09 0 Plant 2-10 1 I would like to create a Top 10 list of the highest values in G3:G72 and then return both the Plant Name and its associated number of accidents in descending order on another tab. Several of the plants will have the same number of accidents (e.g. 0 or 1 or 2) in which case I would need all of those plants. I tried using the following formulas on the different tab but it doesn't appear to work. The first formula is to give the plant name and is started in row A1 of the tab called "Sheet1" and the second is to give the number of accidents: =INDEX('Current Year Data'!A$3:A$72,MATCH(LARGE('Current Year Data'!G$3:G$72,ROW(A1)),'Current Year Data'!G$3:G$72,0)) =INDEX('Current Year Data'!G$3:G$72,MATCH(LARGE('Current Year Data'!$G$3:$G$72,ROW(B1)),'Current Year Data'!G$3:G$72,0)) Any help wold be greatly appreciated. Jeff |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Top 10 List
This is more complicated than it appears on the surface (especially when you
need to account for ties). A top n list can be more than n. Based on the sample you posted the top 10 would include *every* entry so your top 10 list turns into a list of 20. Try this.... Enter this formula in J3. This will return the count of items that fall within the top 10. =COUNTIF(G3:G72,"="&LARGE(G3:G72,10)) Enter this formula in K3. This will return the top 10 numbers in ascending order. =IF(ROWS(K$3:K3)<=J$3,LARGE(G$3:G$72,ROWS(K$3:K3)) ,"") Enter this array formula** in L3. This will return the plant names. =IF(K3="","",INDEX(A$3:A$72,SMALL(IF(G$3:G$72=K3,R OW(G$3:G$72)),COUNTIF(K$3:K3,K3))-ROW(G$3)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Select both K3 and L3 and copy down until you get blanks meaning all the data has been extracted. -- Biff Microsoft Excel MVP "Jeff Gross" wrote in message ... I have two columns of data A3:A72 and G3:G72. The first column has plant names and the second column has a number of accidents. Below is a sample of the data: Plant 1-01 1 Plant 1-02 0 Plant 1-03 0 Plant 1-04 0 Plant 1-05 0 Plant 1-06 2 Plant 1-07 0 Plant 1-08 1 Plant 1-09 0 Plant 1-10 2 Plant 2-01 0 Plant 2-02 0 Plant 2-03 0 Plant 2-04 2 Plant 2-05 0 Plant 2-06 0 Plant 2-07 0 Plant 2-08 1 Plant 2-09 0 Plant 2-10 1 I would like to create a Top 10 list of the highest values in G3:G72 and then return both the Plant Name and its associated number of accidents in descending order on another tab. Several of the plants will have the same number of accidents (e.g. 0 or 1 or 2) in which case I would need all of those plants. I tried using the following formulas on the different tab but it doesn't appear to work. The first formula is to give the plant name and is started in row A1 of the tab called "Sheet1" and the second is to give the number of accidents: =INDEX('Current Year Data'!A$3:A$72,MATCH(LARGE('Current Year Data'!G$3:G$72,ROW(A1)),'Current Year Data'!G$3:G$72,0)) =INDEX('Current Year Data'!G$3:G$72,MATCH(LARGE('Current Year Data'!$G$3:$G$72,ROW(B1)),'Current Year Data'!G$3:G$72,0)) Any help wold be greatly appreciated. Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
create new list from list A, but with exclusions from a list B | Excel Worksheet Functions | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions |