# Ranking Multiple Columns And Second Place

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).

## 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]...

## Ranking Data With Multiple Columns?

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

## Ranking Based On Multiple Columns?

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

## Ranking Multiple Columns By 1000th Inch

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.

## Ranking Without Zero And Ranking On Multiple Factors?

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.

## Automatically Place Player Based Results & Player Ranking

Feb 22, 2009

i m creating a formula that will automatically place players in the correct position based on the outcome and there rank.

I have created an example sheet and the results should be in cells E3:E15

## Ranking By Data In Two Columns?

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.

## Ranking Two Columns Plus A Third (stipulation)

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?

## Ranking Based On Two Columns?

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.

## Calculate Ranking Based On Results Of 2 Columns

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.

## Ranking By Multiple Criteria

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.

## Ranking Values And Ranking Duplicates The Same Rank

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

## Ranking Values Against Multiple Criteria

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.

## Use Numbers For Columns In Place Of Letters

Feb 4, 2009

Is it possible to use numbers for columns in place of letters? If so, can you specify the starting number?

## Ranking Based Off Multiple Ranges In Same Column

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.

## Multiple Criteria Ranking To Ignore Duplicates

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.

## Day 1 (Col B) By Using The Start And Finisg Columns Place A 1 In Column

Jun 16, 2007

I have been trying to see if I can peform the following, I want to be able to have Day 1 (Col B) by using the Start and Finisg Columns Place a 1 in column H for every hour between the Start and Finish times.

This needs to be done for each of the Day in column B, ie 7 Days.

Coulmn's FGH is repeated for the 7 days....

## Compare 2 Columns & Place Unique Values In A 3rd

Feb 12, 2008

I have a database with 2 un identical columns: A, and B. Each cell in Column A should have an equivelant cell somewhere in column B. I want a code that could compare each cell in column A with each cell in Column B. Cells in A that don't have equivelant in B should have their values printed in column C.

## Summary Sheet Update Keeping Set Columns In Place

Apr 7, 2009

using code to take raw data from one workbook <book1> tab <Phase1RawData> and pasting to summary workbook <book2> onto <SummarySheet>. New data is available with each Phase (14 in all) and will eventually fill all cells as indicated in the Summary Sheet Page. However raw data has in any one Phase only the columns up to the Phase its currently at. So Phase 1 will only have Phase 1 column, Phase two will have columns for Phase 1 and 2, Phase 3 will have 1,2,3 and so on (the example Book1 has two example sheets for phase 1 and phase 2 data - so each phase has a column added with the previous columns remaining.)

The problem is in having the summary sheet always show all 14 phases colmns (as in the example attached) irrespective of which Phase is being updated. So if up to Phase 3, insert the raw data available will be colmns for Phase 1, 2 and 3 - but I need ensure after that has been updated only the remaining Phase colums to 14 show. That is, if Phase 1, 2 or 3 etc are now in place, insert the remaining blank Phase columns to, and including Phase 14. Need to do this at end of each phase until 14 is met. The data up to column S is all from the raw data original sheet - Items from Column T I insert independantly as the data is transferred.

## Extract Text From Column :: Place Into Separate Columns

May 14, 2007

I have a long column of info as below. I want to extract (the name changes)- "SEAPORT TWN" etc and also "01.30" and place into separate columns say G and H at the next available cell.

**
SWITCHLIST FOR TRAIN---Grain Spec -East
DEPARTURE TIME from WESTTOWN is 01:00
TOWN STOP---SEAPRT TWN Arriving at 01:30

PICKUPS
Terminal Shipping Burl North 460020 GRAINCAR empty Elevator Co. Ltd.
Terminal Shipping Sante Fe 100396 GRAINCAR empty Elevator Co. Ltd.
Train should leave this town with 2 car(s)
TOWN STOP---PRARIE TWN Arriving at 02:00

SETOUTS
Elevator Co. Ltd. Burl North 460020 GRAINCAR Grn w/Wht Lttrs -4 Bay
Elevator Co. Ltd. Sante Fe 100396 GRAINCAR Maroon w/Wht Ltr -3 Bay
1/05/2007 8:57:52 a.m. ...............................

## Countif (check The Data For The Following Conditions, If It Meets The Crirteria Then Place A 1 In Columns)

Aug 22, 2009

the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.

Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)

What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.

Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D

Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.

## Macro To Copy And Place Data To Specific Place

Feb 22, 2007

I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.

## Multiple Cell Values Into A Variable - Then Place In Another Range?

May 17, 2014

how to set a cells value into a variable, using .value, then set another cells value equal to that variable without using copy/paste

What I can't figure out is how to see the value of multiple cells to a variable and place them into another range of the same size using .value. It would be nice to free up the clipboard.

## Place Value In Multiple Cells Based On Cell Variable

Nov 26, 2009

I'm trying to place the value of "X" in multiple cells based on a cell variable (i). For example, if (i) is evaluates to 35, the following code ...

## Pulling For Multiple Worksheets To Place Into One Master Worksheet?

Feb 8, 2013

I have a work book with prob close to a thousand individual work sheets in it. I have a goup of cells that are the same in ever work sheet that I would like to pull and place into a master work sheet. I dont want to copy and paste each one, I'd be a hundred before I finished and I dont have that kind of time. Im thinking a macro might be what I need but Im not very good with useing them let alone making one to fit me needs.

## Automating Pivot Table To Check Multiple Items And Place Them Into Values Field?

Jul 20, 2012

I am trying to automate creation of pivot table, where the first three fields will go into the RowLabel field, and the rest of them will go into the values as "Sum of ____".

Is it possible? Because I could do it one by one; however, those fields are different every time (there are different number of those fields, and their values are also different).

## Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table

Oct 19, 2006

I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.

| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | \$100 | \$250 | \$300 |
+------+------+------+------+--->
| Feb | \$200 | \$300 | \$200 |
+------+------+------+------+--->
| Mar | \$300 | \$250 | \$100 |
+------+------+------+------+--->
| Sum | \$600 | \$800 | \$600 |
| +------+------+------+--->

| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | \$100 | \$250 | \$300 |
+------+------+------+------+--->
| Feb | \$200 | \$300 | \$200 |
+------+------+------+------+--->
| Mar | \$300 | \$250 | \$100 |
+------+------+------+------+--->
| Sum | \$600 | \$800 | \$600 |
| +------+------+------+--->

Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #

The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?

## Excel 2007 :: Automatically Transfer Columns From One Worksheet To Another In Multiple Columns?

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].....

## How To Combine Multiple Columns Into Long List Of Results In 2 Final Columns

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?