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:
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.
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.
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.
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?
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.
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.
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.
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?
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.
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.
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 ?
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.
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
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
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".
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
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 |
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.
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).
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.
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.
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.
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
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.
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.
