# Vertical Ranking Of Unique Values

Feb 20, 2010
I've been trying to figure where I've gone wrong on this one for a couple days. Maybe you guys can assist.

I have a data entry table:

DateAgeGenderPositionSportAthleteBike 5 min (Miles) 1/2 BW Pull Up1/8/200920-26MaleChangerRB_84Brett Nena8351/8/200927-33MaleJackRB_83Sha Pet3381/8/200927-33MaleGasRB_82Mik Malf1551/8/200948-54MaleGasMWR_56Dou New2321/8/200920-26MaleChangerRB_82Cha Avr12401/8/200927-33MaleChangerRB_82Dan Kaid6181/14/200927-33MaleChangerMWR_00Ryan Lang7121/14/200927-33MaleJackRB_83Sha Pet6331/14/200920-26MaleCarrierMWR_00Don Mar9152/8/201020-26MaleChangerRB_84Brett Nena8121/12/201041-47MaleCatchMWR_00Jeff Sea715

I have an area for each exercise where I am ranking the athletes average score. In these ranking areas I did not want the athlete name to show up more than once so I decided to use this formula in the names column below:

{=IF(ROWS(L$2:L2)

View 9 Replies
ADVERTISEMENT
Apr 1, 2014

I'm making an excel file where I keep track of the scoring in a little competition me and my friends do with betting on sports.

However, I got a little problem trying to automate my ranking.

Naamloos.jpg

As you can see in the image, when there is a duplicate score, the name stays the same for every other same value (Joris). The formula I use (also as seen in the image) just takes the first name it comes across (from left to right) and keeps using that one. But I need it to ignore the name Joris the second time, and ignore Joris AND Tim the third time, so that every score/rank (even if the scores are a draw) has a unique name attached to it.

View 3 Replies
View Related
Mar 3, 2014

In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.

Example: CT11:CT14 looks like

5x20

6x4

5x20

5x8

and so on and so forth..

I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.

View 4 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
Dec 6, 2013

I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is:

=RANK(A1,$A$1:$A$890,0)+COUNTIF($A$1:A1,A1)-1

To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there.

A source below really shows the formula should work. [URL] .......

View 2 Replies
View Related
Jan 19, 2012

how do you create a graph with a field such as vendors on the X-axis, but with dollar values spent on the left side of the y-axis and percentage of total dollars spent on the right side vertical axis?

View 3 Replies
View Related
Mar 26, 2014

I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.

Formula:

[Code] ....

View 2 Replies
View Related
Jan 22, 2014

I have a data base of numbers Columns C to K with values from Row 2 to 3766. I want to rank each of these columns by value 1 to 100. However, some of the columns will assign 1 to the highest value and 100 to the lowest. In other columns it will be reverse, 1 is given to the lowest value and 100 to the highest. I can do a rank by percentile but that's not the scoring I'm looking for. Also don't know how to reverse assignment of high or low values. One further note, some of the cells are blank in which case I want to assign a neutral score of 50.

View 2 Replies
View Related
Mar 22, 2014

I have a results table that i want to be able to rank into a simple list 1 - 4. However the results are not in cells beside each other.

I tried using a helper column and then the Large function, however cannot get it to work.

I have attached an example sheet, and in column L4 - L7 i would like to put the team names (taken from column B) into the right order from the results shown.

I have to replicate this over a number of different pools in a number of different divisions. This is just one pool.

View 10 Replies
View Related
Jan 29, 2010

I'm trying to let the code described down below to select 10 of the highest values and give them a ranking in the next column using the letters A to J.

My query;

The code gives all 10 of the highest values the ranking letter J instead of A to J, so what's going wrong?

Also, is there a way to simplify this code so it will be easier to maintain and run quicker?

View 6 Replies
View Related
Oct 6, 2009

I have 2 lists.

I want to rank scores/ratings in list 2 and rank against all those cells that have the same value.

I will upload an example.

In the example you will see column A and column B.

I need a Rank for all those cells in B for 17:45, 18:15, 18:45 etc.

View 9 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
Nov 28, 2008

I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.

View 4 Replies
View Related
Aug 24, 2009

If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?

View 14 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
Feb 20, 2013

I want to put vertical values to a selected space with horizontal orientation

See the excel file : FILL IN SELECTED DATA.xlsx

View 9 Replies
View Related
Aug 27, 2013

I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.

Here's the before picture:

column A column B column C column D column E column F

Part num 1/1/2013 1/8/2013 1/15/2013 1/22/2013 1/29/2013A

100 100 100 100 100B

150 150 150 150 150C

200 200 200 200 200D

250 250 250 250 250

The output after manually doing this would look like this:

column A column B column CPart num Date QuantityA

Jan-2013 500B Jan-2013 750C Jan-2013 1000D Jan-2013 1250

View 5 Replies
View Related
Mar 7, 2013

I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.

"

A B C D

| SUMMARY

model qty| modelqty

1 4.12922.0000| 4.12952.2000

2 2.000012.1250| 2.000025.1250

3 4.12929.0000| 318.0000

4 318.0000|

5 4.1291.2000|

6 213.0000|

"

A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model

View 1 Replies
View Related
Mar 2, 2014

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

View 3 Replies
View Related
Sep 15, 2013

I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)

The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.

Sample data Required Output

A | B Z

1| 10 | a 10,a,b,c

2| 10 | b 11,a

3| 10 | c 12,a,b

4| 11 | a

5| 12 | a

6| 12 | b

My system is Windows 8 Excel 2010.

View 7 Replies
View Related
Aug 14, 2014

1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).

I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)

2. To include all unique string values except those starting with the letters "IC"

3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.

Here is the formula I currently use which serves #1 (above):

[Code] .....

Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.

Example of desired results:

Column A | Column B

AA | AA

DD | CC

AA | DD

CC |

DD |

DD |

IC |

IC |

View 14 Replies
View Related
Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is

{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))

+

SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3

All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

View 2 Replies
View Related
Apr 17, 2009

I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.

What would be the best way to do so?

Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.

I have attached an example file that represents how the data is stored and the output I would like (Unique Count).

View 7 Replies
View Related
Mar 1, 2014

I have a spreadsheet with golfers handicaps, golfers names down the left in column A in rows 3-35. Row 2 has the event numbers titles 1 to 18 (18 events in a year). i need a graph that has the person as the vert axis and the horizontal axis needs to be the event numbers, so i can read down for the person and across to see how there handicaps change from each different event. i have attached the data below.

View 1 Replies
View Related
Jan 31, 2014

how to get this with formula:

Column A - - Column B

a - - - - - - - a

a - - - - - - - b

b - - - - - - - c

c

c

View 8 Replies
View Related
Sep 13, 2006

I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))

Now, what I would like to do, is see how many of these unique numbers relate to another number in column D

See attachment.

View 4 Replies
View Related
Feb 11, 2009

What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.

ABCD

1Non UKUKRank

2Cat20055

3Dog400101

4Eel200114

5Frog40072

6Goat30023

View 4 Replies
View Related
Mar 4, 2011

I have number values in G2:G50 with some blanks included - what I'm looking for is a formula that will add all the values together but will only add values that exist more than once one time.

4.24

2832.74

2832.74

8643.73

8643.73

The only things that should get added at 4.24, 2832.74 and 8643.73

View 9 Replies
View Related
Dec 4, 2012

I have an ever growing client list that currently has just over 2500 names which I need to track the total number of clients, which is the easy part, the total number of files, and the most important part is how many unique names are on the list. Some clients will appear a number of times on different dates, and I need to be able to track how many clients appear only once in the list.

Adams, John

10/6

Lincoln , Abraham

10/6

Clinton, Bill

10/6

[code].....

In the above example, the answer I am looking for is 2, because Clinton, Bill and Carter, Jimmy both appear only once. My total clients would be 4, though the number number of "files" worked on would be 6.

View 3 Replies
View Related
Feb 6, 2007

I have some data and one of the columns is overtime. The data is set up where each employee has a record for each function they perform. So I may have an employee that opens and funds loans. They will have 2 records and one of the fields in the record is overtime. My issue is, in this example, that the OT will be doubled if I just sum the column. I need to sum the unique values only.

View 9 Replies
View Related