Ranking With Multiple Columns
Apr 26, 2014
I have a project that I am unable to figure out the best way how to accomplish. I have five players on our basketball that are having a contest. Each player took 25 shots. I need to rank each player's shots individually. For instance, Andrew made 24 and 23 (4th and 1st attempts respectively) so he should be 1st and 2nd while Curtis would get 3rd because he was next with 22 made shots on his last attempt.
I would like to rank the numbers multiple columns (columns B thru F) in column G (not shown but understood).
If this is not possible, is there a way to list ALL the numbers in column G using a formula (the number of students will continue to grow so I am unable to simply enter =B2 in cell G2 and restart with =C2 in cell G7 (after five entries) because the number of students will grow.
A
B
C
D
E
F
[Code]...
View 3 Replies
ADVERTISEMENT
Feb 14, 2014
I have a spreadsheet with values as per below
I can rank the data based on descending values in Columns B & C using the formula in cell B2
=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11>C2))
What I trying to do is also rank the data based on the values in column D as an additional tie-breaker
RankScore 1Score 2Score 3
1412315
2402420
2402422
4402030
5392015
View 3 Replies
View Related
Feb 14, 2014
I have data in a number of columns in Excel which I want to sort on column B, then C, (Then D) however the formula I have entered is sorting based on column C below in ascending rather than descending order, how I can change this
=RANK(C2,$C$2:$C$4)+SUMPRODUCT(--($E$2:$E$4
View 2 Replies
View Related
Mar 25, 2007
******** ******************** ************************************************************************>Microsoft Excel - Win_calc.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutQ21=
QRSTUVWX21TickerLowestLowest*NameBOALEHBEARSecond*priceSecond*name22AT*US95/BEAR10210095**23ARW*US50/LEH605070**24BSX*US65/LEH666567**25CBS*US53/BEAR/MS555453**HVOL8*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is my general template. I get prices from BOA, LEH AND BEAR on the four companies listed. Then I use the Min function to tell me the best price (I'm buying). Then I use some IF statements to tell me who's the "Lowest Name" (lowest price):
=IF($G2=$C2,"/"&$G$1,"")&IF($H2=$C2,"/"&$H$1,"")&IF($I2=$C2,"/"&$I$1,"")&IF($J2=$C2,"/"&$J$1,"")&IF($K2=$C2,"/"&$K$1,"")&IF($L2=C2,"/"&L$1,"")&IF($M2=$C2,"/"&$M$1,"")
It checks if a cell is = the lowest cell value, then returns the header. This formula refers to different columns than my displayed sheet because Im hiding so cells and I just copied this to another section of my sheet. There's probably a better way to do this but this is what I have do far. I use this for 50 company names and 5 or six dealer banks (BOA, LEH, etc)
I'd like column that gives me the Second Place price and one that gives me the associated dealer (BOA, LEH).
View 9 Replies
View Related
Jul 5, 2006
I have the following data (measured in 1/1000th of an inch)(I have rounded down in this example though!!!)
NameABCDEAGGReal RANK
AAAAA0.110.120.090.120.270.1422
BBBBB0.120.1140.080.1350.2610.1421
CCCCC0.350.170.170.160.110.1923
copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5)
When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they both have AGG of 0.142. Problem is the rule book states ties are split by the smallest of the results for each competitor in columns A to E. In this example BBBB is 2nd as he has smallest result in Column C being 0.08.
I have tried the AGG+1/min(a:e) and still end up with incorrect results. The problem is the numbers are so small and you have to split ties all the way to the 100th person involved. I cant use sort either as the data has to be printed in alphabeticall name order all the way to 100 names with their ranks recorded against the names.
View 11 Replies
View Related
Aug 12, 2014
I am facing some problem with ranking formulae. Attached is a file illustrating them.
Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.
Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.
This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.
Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.
View 1 Replies
View Related
Apr 4, 2014
I am trying to rank via data in two columns to eliminate any tied rankings.
I need to use the data in columns "AW" & "AX" to compile the rankings.
The ranking needs to be calculated by the highest number in column "AW" being ranked #1 and the second highest ranked #2 etc etc......any ties as a result of this ranking need to be broken by the lowest number in column "AX", the lower number being the higher in value and thus being the higher ranked.
For example:
AW AX RANK
13 89 7
13 63 4
14 89 2
14 81 1
13 90 8
13 66 5
13 58 3
13 87 6
I have attached the spreadsheet that I am using.
View 7 Replies
View Related
Jan 20, 2014
Running a NFL playoff pool and just actually used a previous post to finally correct my Rank formula which was great.
Basically I want to rank based on the POINTS column, and then based on the WINS column, and then based on two other things. The final tiebreakers are as follows.
1. Most points
2. Most wins
3. Picking the Super Bowl game correctly
4. Closest to the total points of the Super Bowl game (everyone has to pick what they think the total score of the game will be).
This is the formula used to Rank Points and Wins:
=RANK(M8,$M$8:$M$190)+SUMPRODUCT(--($M$8:$M$190=$M8),--(N8<$N$8:$N$190))
Can I add IF Statements to RANK formulas?
View 1 Replies
View Related
Jun 18, 2014
i would like to rank one table based on two columns, for example world cup group table to be ranked based on points but if two teams have the same no of points, then use goals scored column to break tie between them.
View 9 Replies
View Related
Nov 4, 2008
I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD)
In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.
View 9 Replies
View Related
Oct 3, 2008
I have attached an example worksheet of something that I am working on. My references refer to this sheet.
Column Descriptions:
B = agents.
C = the agent's supervisor.
D = the agent's score.
E = the agent's overall rank in comparison to all other agents.
*F = the agent's rank in comparison to the other agents on the supervisor's team.
I need a formula (non-VBA) that will fill column F. That is, a formula that will compare agents on a specific team, based on their QA scores. Though the example data range is small, this will be applied to roughly 600 agents, split up among roughly 40 supervisors. I am currently using Excel 2007.
If anybody has any ideas, suggestions, or solutions, I would greatly appreciate the help, as I have been beating my head against the desk in my attempt to figure this out.
View 2 Replies
View Related
Mar 30, 2014
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
Example:
20
18
18
15
12
11
11
9
The ranking I am looking for is:
1
2
2
3
4
5
5
6
View 2 Replies
View Related
Oct 7, 2009
I have successfully been able to rank items using two criteria but I'm unsure of the syntax for adding a third or fourth set. I'm new to the use of Sumprodoct.
I have attached a file. Where column C and E have the same value, I want Excel to look at column F and assign a rank (formula is in column K) in ascending order.
ACEFK
ItemLife LeftTot CostBalRank
signs1 250 250 1
paint curb1 500 500 2
tree work1 2,500 2,500 3
landscape2 9,000 7,200 4
siding2 9,000 7,500 4
Here is my formula:
=IF(ISERROR(RANK(C8,C$5:C$105,1)+ SUMPRODUCT(--(C8=C$5:C$105),--(E8>E$5:E$105))),0,RANK(C8,C$5:C$105,1)+SUMPRODUCT(--(C8=C$5:C$105),--(E8>E$5:E$105)))
I've attached a file.
View 7 Replies
View Related
Jan 1, 2014
I have a list of research material based off postcodes, travel time in those areas and what I want to do is rank the the travel times in those ares within a third column.
What I have so far in design terms is this:
Origin Base
Time
Time Ranking
AB10
00:10:00
AB10
00:12:00
[Code]...
So basically I'm trying to have column c look at a and check for the lowest value in b against all other rows that have the same constant value e.g. AB10 in column a. Then to distinguish them somehow for example marking the first '1'. Also then to be able to do it for second and thrid ranking value.
What do people think? Is this doable? I think maybe a min if array but keep tripping on the fact that the ranges are all locked in a single column and inconstant sizes.
The data comes in this format and is quite large so manual editing would be a last resort.
View 8 Replies
View Related
Apr 2, 2008
I posted a thread a while ago asking for a formula to rank based on multiple criteria (Rank On Multiple Criteria). The following; = SUMPRODUCT(--($A$2:$A$50=$A2),--($B$2:$B$50=$B2),--($D$2:$D$50=$D2),--($G2>$G$2:$G$50))+1)
works an absoulte treat, however is there a way to modify it so that does not allow duplicates? The data in the G column refers to time values acheived during testing of athletes, the problem arises when two athletes achieve the same score and thus they are ranked the same. However, body mass is also measured (F column) and in essence the athlete with the lower body mass has achived the better score and thus should be ranked accordingly.
View 3 Replies
View Related
May 4, 2013
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
Excel 2007
B
C
3
Name
Company
[Code].....
View 9 Replies
View Related
Oct 15, 2013
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
View 4 Replies
View Related
Apr 30, 2014
I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.
I have create a spreadsheet with the data and the result that I typed into the "result" column.
View 3 Replies
View Related
Aug 6, 2009
Example:................
The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............
View 7 Replies
View Related
Feb 15, 2010
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:
Sub CopyToA()
Do While ActiveCell <> ""
Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0)
ActiveCell.Offset(0, 1).Select
Loop
End Sub
However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.
View 2 Replies
View Related
Feb 2, 2008
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
View 2 Replies
View Related
Feb 24, 2013
I have a large database of research data where the first column contains different categories (labelled as I, II, III, IV, V, and VI). For each row there is a data value in the second column that is numeric. What I need to do is move the data so that the data are displayed in 6 columns with the data values listed each column heading according to the category label. In the example I've shown there are 6 different categories, but the number of categories (and hence the number of columns in the final resulting sheet) will change for different data sets that I'm using. Also note the the number of cases for each different category is different (so that each column will not be even in terms of the number of rows of data under each column heading). I hope I've been able to explain what I'm looking for clearly.
I've attached a sample file showing the sample data input that I have on one sheet, and then the desired re-tablulated outcome on the second. I have done this using the auto-filter function and copy/paste, but this will take far too long for the larger datasets I'm working with.
View 2 Replies
View Related
Dec 30, 2009
I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times.
View 3 Replies
View Related
Mar 9, 2012
I have columns A through AKP (A:AKP) with data, and there is a varied number of rows for each column. If possible, I need a macro that will "stack" columns in pairs of two. For example, column "C" directly under column "A" and column "D" directly under column "B" and so on for all columns A:AKP.
This is an example of what my data looks like:
COLUMN A COLUMN B COLUMN C COLUMN D
ROW 1 2598 F800 2599 F800
ROW 2 2598 K1300 2599 K1300
[Code].....
View 3 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
Nov 7, 2009
I have 8 different files all have a set of data in them
each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank.
I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
View 9 Replies
View Related
Sep 28, 2012
Want a single count of multiple columns based on the columns selected value. Data is in text format.
Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?
In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.
Sample data:
Pegnancy Smoke
Pregnancy Alcohol
Pregnancy Marijuana
Pregnancy Powder
Stress Cigarettes
Stress Marijuana
Stress Alcohol
Stress Medication
[Code] .....
View 2 Replies
View Related
Jul 28, 2013
I'm making a Excel list for trading cards (MTG) and I've divided it into different worksheets in order to sort it by the color of the cards (not really important I guess).
The issue I have is that I want it to copy certain columns (in this case C3:C1000, F3:F1000, G3:1000 ) from almost all of the worksheets into a new worksheet where it should paste them into column A, B and C.
In two of the worksheets it's also different (C3:C1000 , G3:G:1000, H3:H1000).
I've seen many where you copy one range from multiple worksheets, and that would've been okay I guess if not those two worksheets were different. I'd prefer not to change them, and I'd also prefer if I could've gotten just the information I needed also. Is this possible to do?
View 5 Replies
View Related
