Golf Scoring But Have Duplicate Scores - Return Unique Name

Jul 28, 2014

I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.

Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.

Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))

Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))

Column A = golfers names

Column L = list of all golfers scores

Column N = lowest ten scores in order

View 4 Replies


ADVERTISEMENT

Golf Scoring But Have Duplicate Scores - Need To Return Unique Name

Jul 28, 2014

I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.

Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.

Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))

Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))

Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order

Spreadsheet Attached : Golf Excel Score.xls‎

View 11 Replies View Related

Scoring To Break Tie (i.e. Golf)

Oct 7, 2006

I couldn't find a solution to where I'm at now, but Derks formula...
= SUMIF($J$5:$J$44,J5,$K$5:$K$44)/COUNTIF($J$5:$J$44,J5)

from...
excel formula

...Almost gets what I need, but my data will not always be in a high/low descending order. I'm braindead; I can't think of how to make this formula work.

View 9 Replies View Related

Statistics From Golf Scores

Mar 14, 2008

I am creating a workbook to manage golfer scores, teams, winnings, handicap, ect. I am having trouble with the statistics sheet. I Need A Sub To:

1. Column "B" , take the average of the lowest 5 numbers in columns "AB" though "AU"

2. Column "C" , take the average of the lowest 10 numbers in columns "G" through "Z" divided by 0.96

3. Column "D" , take the average of the lowest 10 numbers in columns "AW" through "BP"

this needs to happen for each row where there is a name. (names added daily)

I have included a similar sheet as the one i am working with along with some command buttons typically not included. Should show some of the problems I am having.

View 5 Replies View Related

Golf Scores: Average X Lowest Of Last X

Jan 15, 2006

I would like to set up a Excel spreadsheet for golf handicaps where it will use the 4 lowest scores of the last 5 entries. It must be able to work even if there are missed entries (someone didn't show up/blanks ignored). I would like it to work if there are only three scores used at the beginning of the season, but when there are 5 or more scores, use only the 4 lowest scores out of the last 5. I want it to average these scores. The scores would start in column d. The following is an example of scores with an underscore being a blank:

Golfer 1 45,50,42,43,46,37,45,45,46,44
Golfer 2 41,43,42,_,41,_,_,47,42,39
Golfer 3 44,43,45,54,_,45,43,40,42,_

View 9 Replies View Related

Spread Sheet To Get Golf Handicaps Using The Lowest 5 Of Last 6 Scores

Apr 1, 2008

I need a formula to figure the 5 lowest of last 6 sores, since everyone doesn't play every week there would be blank spaces and it needs to just give me the average if they have less than 6 scores!

I have manipulated the formulas until I am blue in the face and cannot get it to work;

View 9 Replies View Related

Filtering Duplicate Values With Distinct Scores

Feb 24, 2014

I have an excel which is containing records of users who have given an online test.Many users have given the test multiple times. I need to extract the records of every individual with the highest score. example it could be individual A, B and C have given the test 3 times with different scores achieved. I need the records of A, B and C in which their score was maximum.

View 7 Replies View Related

Deleting Row Having Duplicate Unique Id?

Apr 29, 2013

VB:
Sub aa()
Dim roow As Long
Dim i As Long [code]...

View 3 Replies View Related

Filtering For Unique And 1 Duplicate

Aug 7, 2009

create a lookup table in access from this excel spreadsheet. Column : "Unit Name" - has some unique and majority are duplicates Unit Names. I want to filter for Unique names and for one name of each duplicate. This will give me all "Unit Name"'s we've entered to date.
When I enable filters for the headings of each column, The list of what I need is there...while in the "Unit Name" Column. If I click on the filter drop down....that the list. My thing is how do I get that on the sheet it self.

View 3 Replies View Related

Find Top X Scores Out Of 50 Scores?

Aug 17, 2009

There are scores in B1:B10 (10 scores). I want to take the top 8 scores. So I used this formula which works just fine.

=SUM(LARGE(B1:B10,1),LARGE(B1:B10,2),LARGE(B1:B10,3),LARGE(B1:B10,4),LARGE(B1:B10,5),LARGE(B1:B10,6) ,LARGE(B1:B10,7),LARGE(B1:B10,8))

But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?

View 5 Replies View Related

Map Scores To Scaled Scores

Feb 13, 2008

I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.

View 2 Replies View Related

Merging Duplicate Rows With Unique Data?

Jun 5, 2013

I have attached a before and after image of what I am looking to accomplish.

In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)

In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number

What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.

Is there a way to accomplish this via some functions or maybe even a VBA script?

View 3 Replies View Related

Delete Duplicate Row Based On Unique ID In Column A

Feb 23, 2012

I have a set of data I'm trying to clean up. I'd like some code that would look at column A, and if a duplicate is found in A, delete the duplicates entire row. Column A contains a unique identifier code.

View 9 Replies View Related

Auto Add Unique Id When Duplicate Numbers Exist

Dec 10, 2008

I receive a csv spreadsheet in which Column A contains ID's like this

LS-X23268370.944849537037037
LS-X23181010.445011574074074
LS-X23181010.444930555555556
LS-X23399640.612361111111111
LS-X23399640.612395833333333
LS-X23399640.612407407407407
LS-X23399640.612395833333333
LS-X23399640.612395833333333
LS-X23399640.612407407407407
LS-X23399640.612407407407407

As you can see, some of these are identical. Fort example there are 3 copies of

LS-X23399640.612395833333333

I'd like to run a macro that effectively looks for duplicates and appends a letter to the end so the example above I would end up with..

LS-X23268370.944849537037037
LS-X23181010.445011574074074
LS-X23181010.444930555555556
LS-X23399640.612361111111111
LS-X23399640.612395833333333a
LS-X23399640.612407407407407a
LS-X23399640.612395833333333b
LS-X23399640.612395833333333c
LS-X23399640.612407407407407b
LS-X23399640.612407407407407c

View 9 Replies View Related

Add Duplicate Values For Multiple Columns From Unique Data..

Jul 13, 2009

i would like to paste multiple duplicate values from unique values. Is there any way to do it in excel??

Ex:

Table 1:

3MG_HUMAN453
3MG_HUMAN44
3MG_HUMAN953
A1AT_HUMAN285
A1AT_HUMAN76
A1AT_HUMAN234
A1AT_HUMAN653
A1CF_HUMAN96
A1CF_HUMAN23
A1CF_HUMAN765
A4_HUMAN 944
A4_HUMAN 23
A4_HUMAN 755

Unique table

3MG_HUMAN100
A1AT_HUMAN78.89
A1CF_HUMAN90
A4_HUMAN 98

I wanted to add new column with these values for table 1 including duplicates. this is sample data and i wanted to replace thousands of this type.

View 7 Replies View Related

Code To Copy Unique List - Gives Duplicate In First 2 Rows

Aug 29, 2012

I am using the following code to copy a unique list, but it gives a duplicate in the first 2 rows(col Q). There is no headings in the columns.

Code:
Sub CopyUnique()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "P").End(xlUp).row
ActiveSheet.Range("P11:P" & lastrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("Q11"), Unique:=True
End Sub

View 4 Replies View Related

Macro That Merges Duplicate Rows Based On Unique Values

Jan 27, 2014

I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.

The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.

Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?

View 2 Replies View Related

Deleting Duplicate Row (8 Cells) And Shifting Unique Rows Up - 3 Columns As Filter

Feb 4, 2014

I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).

As filter I would like to be used A,B,C columns.

EXAMPLE:
BEFORE
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

AFTER
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1

Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

View 1 Replies View Related

Combining Unique Data Into One Row With Columns While Merging / Deleting Duplicate Information

Jun 13, 2014

I have data in excel that has some information the same with a unique field. See below:

Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1

[code]...

I want it to consolidate all of the like information but add on the codes in separate columns like this:

Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4

[code]...

Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.

View 5 Replies View Related

Count Unique Values Based On Duplicate Status In Another Column On Filtered Data

Mar 5, 2014

I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).

What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.

Picture:
Column C Column D
111111 M
111111 M
111111 M

[Code]....

Currently calculates: M=9, F=2

Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.

Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))

View 9 Replies View Related

If Cells Are Duplicate, Then Return A Value

Sep 24, 2009

Is there a formula I can use in B2 whereby if the value in A2 is identical to the value in any other cell in column A, then return the value "duplicate" in B2?

View 2 Replies View Related

Scoring Conversion

Nov 4, 2008

If I have a scoring grade for different categories (8 in total) & the grades looks something like that: 1a=500, 1b=400, 1c=300, 2a= 250, 2b=210...& so on till 4c, but with different numbers for each category.

I need to come up with a total score of points if a person is given a certain grade from the above. ex. if they scored 2a on a certain category, 1c on
another & with all the rest of 8 categories scored. The number i need to come up with is the total points.

View 9 Replies View Related

Weighted Scoring

Mar 15, 2008

I'm doing a GCSE project for which I need a little help with Excel.

I have a set of data about a persons monthly spending habits on

Food 300
Entertainment 200
Clothing 100
Transport 260

The pre calculated budget allowance for the month was 500

I would like to write a formula that would do the following; give the person an score out of 1.1-10 based on how well he spent on the above items which are in order of priority (i.e. it is better to spend more on food than on entertainment etc) And then an over all score (1.1-10) that will reflect how well they spent.

View 9 Replies View Related

Scoring Programme

Apr 9, 2009

I am looking for helping with an excel scoring programme i am designing. The programme is ready to go. But i was wondering how clever excel was. Basically i need to create some results from the programme. Normally i do filter auto filter, which is great but not very fast if doing it manually. Would excel be able to do this.

The data i am using is always changing. I would need for it to filter the three teams, by a name defined in three cells one page. Once the three teams have been filtered i then need to get the top four scores and copy and past them into a new sheet.

I have tried recording a macro, but this does not seem to like me changing data. And is also very restrcitive. As the programme is used across three league.

View 9 Replies View Related

Formula For Scoring

Apr 16, 2006

I am not so sure on the correct formula syntax or the correct terminologies to ask this next formula question. It is visualy explained clearer in the spreadhseet provided.

View 9 Replies View Related

Compare Duplicate Items And Return A Value

Jan 19, 2010

if there is a way to find duplicate items in a list and return a value, if a duplicate exists.

Basically I have 19 buildings each with a separate bill of quantities. I'm looking to compare and compile a comprehensive list. So that duplicate items in different buildings can be compared side by side.

I have attached a sample of what I am trying to do.

View 11 Replies View Related

Surrounding Scoring Data

Feb 16, 2009

I have a small query about excel and whilst there is a really simple answer i'm struggling a little bit. I have create a score metric for data surrounding search data.

Basically i need a macro/formula that will take a list of values that may include duplicates and add up the corresponding score and place the results in a table.

See below - the macro will look at a list of values (column b) and add the scores up (column d) and then remove the duplicates and populate the score in (column f/g)

View 9 Replies View Related

Formula To Return 1st Unique Name?

Dec 12, 2013

I have a list that goes something like this

Main
Main
Main
Second
Second
Last
Last
Last
Last
Last

The quantity and text of these labels in the list will always change so they will not fall in the same order

What I want to do is in A1 return the first unique name (Main) then in A2 return the second unique name (Second) and so on until the list ends. I can't perform any kind of filter as I require the value to perform part of a concatenated text

View 6 Replies View Related

Return Unique Value From Range

Nov 1, 2009

Return from a range of cells the value that is unique within the range. Note: assume there is no finite set of unique values that I can lookup within the range. attached workbook. Need direction on a cell formula in B7 that isolates any unique value.

View 5 Replies View Related

Return Offset Details For Duplicate Names

Feb 21, 2008

I have a list of about 1000 names with 2 columns next to it. The first contains a list of different groups that the names belong to (there are 19 possible groups). The second contains a list of figures ranging between £10,000 and £500,000. I want a formula that will look for a name in the list and, when it finds the name, return the corresponding group and corresponding amount. My problem is that some names are in more than one group and therefore appear in column 1 more than once. I have tried to use the LOOKUP formula [eg looking for the GROUP =LOOKUP(Sheet1!B33, Sheet2!A:A, Sheet2!B:B)] but when a name appears more than once I cant get all the results - for example:

Mr Phil LittleGreen Group35,000
Mr Ed GrantRed Group20,000
Mr Robert SmithOrange Group10,000
Mr Robert SmithRed Group50,000
Mr Robert SmithGreen Group75,000
Mr James MorrisonGreen Group100,000

On my summary page (Sheet1) I would like to type in Mr Robert Smith (into cell Sheet1!B33) and in the rows below (obviously in 9 different cells) have the results appear but I can only ever get Mr Robert SmithGreen Group75,000 as a result. This is my first post so I hope I have obeyed all the rules and this is clear

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved