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 RepliesI 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
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
I have a set of cells and i want it to return all of the distinct or unique values within that matrix. for example, lets say this table charts how many sales an employee makes in a week.
sun mon tues weds thurs fri sat
john doe 1 0 3 2 3 0 0
jane doe 0 8 0 1 0 7 0
james doe 3 3 2 3 2 1 1
the desired output would be: 0,1,2,3,7,8
I don't want to be copying all of the cells or doing a lot of manipulation - i know i can copy, sort, and get unique values that way - i want a formula where as i plug in values, this field is updated to include all of the distinct or unique values.
I have a spreadsheet which contains (see illustration below):
1. a column called LEAD ID which contains a unique id
2. a column called WON which signifies if the deal was won/not won (1 = WON, blank = Not Won)
I can have multiples of the same value in the Won and Unique Id field (see illustration below)
I would like to create a formula field which returns the summary of the No. of Deals WON
I need to tie the formula back to the Lead ID and whether or not the WON field contains 1 or not.
I've tried this formula but it won't work
(Lead ID is in Column A, WON is in column B)
=IFERROR(SUM(1/COUNTIF((A:A,A:A) AND(B2=1)),"0")
LEAD ID WON No. Won (Formula field)
abc112 0
abc222 1 .25
abc222 1 .25
abc222 1 .25
abc222 1 .25
abc333 1 1
abc444 0
I'm a huge fan of the DistinctValues function published by C Pearson here: Distinct Values Function
Now I have the problem that I need to get all the distinct values from a 2-dimensional range.
I am trying to count multiple occurences.
Instrument Name
Times Traded
AEON CO LTD
6
ASICS CORP
3
ELECTRIC POWER DEVELOPMENT C
10
In column C (Times Traded) I have used a simple countif formula: =COUNTIF($B$2:$B$8068, C2)
Now I have to check for how many times this instrument was bought or sold. This is in column A .... How will I count the Buy values ... cause this formula I devised doesnt work =COUNTIF($B$2:$B$8068, and(C2, A2:A8068="Buy")
I have this formula that works perfectly in Excel. It counts the unique TEXT values in a column. But it does not work in the VBA editor. Count = SUMPRODUCT((C6:C39<>"")/COUNTIF(C6:C39,C6:C39&""))
View 2 Replies View Relatedcreate 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.
I have a workbook with two worksheets:
Input
Entitlment
On workbook called "Input" range A2 downwards are a series of staff names, non unique
When I select the worksheet called "Entitlement" I would like on this sheet range A2 downwards to list staff names in alphabetical order but only unique occurances of a persons name.
eg if on sheet "Input" cell A2="darrenS", A3="darrenS", A4="EdwardG", A5="EdwardH"
upon selecting "Entitlement" sheet
A2 would show "darrenS"
A3 would show "EdwardG"
A4 would show "EdwardH"
I user will then have to enter a number relating to number of accounts in ColumnB
should a name already have an existing number next to it then when the worksheet "Entitlment" is selected again the number will reamin associated to the name. When sorting occurs !
I'm looking for a SUBTOTAl IF function (if there's any) that would sum all the distinct values in a column for a given criteria based on another column. My data is listed below:
Column A Column B
(Quarter) (Revenue)
Q1 2008 $10
Q1 2008 $10
Q1 2008 $20
Q2 2008 $15
Q3 2008 $25
Q3 2008 $25
I'm looking for a formula to sum the DISTINCT Revenue values for Q1 2008, Q2 2008, and Q3 2008 based on the autofiltered list. The formula result should change dynamically based on the autofilter selection.
I am trying to setup a table for someone to track and schedule service hours on heavy machinery.
I have a table with columns for Make, Model/SN(which is the unique field in each row), date of last service, hours at last service, current hours and an hours since last service. Service needs to be done at 500 hours so I filter the table based on the hours since last service for >= 400 hours. That's fine, but it shows multiple lines per machine. Is there a way to use a custom filter to find duplicate values in the Model/SN column and then only show the row with the max date of last service?
I'm sure this would be a cinch with VBA, but I would like to find a way that if something changes later on after I am gone that he can still possibly figure out how to make it work.
I have a table with 200.000 rows.
I need to create a helper column, that marks distinct values with 1 and not distinct "" or 0.
I tried the following but both use countif and I believe it is the problem it's just to slow.
=IF(COUNTIF($A$2:A2;A2);1;0)
=1/COUNTIF($A$2:A2;A2)
but both are really slow +20 mins to calculate and many times Excel crashes( 2007 32bit).
The workbook is really simple
Col1 (number formatted as text) - Col2 =distinct
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
Sub FilterUniqueData()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)
On Error Resume Next
With Worksheets("Sheet1")
[Code]....
The vba code below copies the selected value to cell C5 whenever the combobox is selected
Copy the code into a standard module.Assign this macro to the combobox
Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value)
End With
End Sub
Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
Call FilterUniqueData
End If
End Sub
My problem is this:
Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,
I have two columns in excel, col A and col B.What I would want to achieve is following:
Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3
Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).
[URL]
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100
101
102
105
Col D
6
6
5
5
I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.
Here is my code thus far....
-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
[Code]....
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100.........................
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)?
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 Relatedeither writing a formula or coming up with some way to filter out certain values and it's stumping me... it's probably way more simple than I'm making it I've got an attendance spreadsheet with employee names going down the first column and dates going across the top row, managers use codes to indicate sick, personal time, etc.
There is a separate spreadsheet for supervisors to track vacation (don't ask me why) and I was asked to link the data from that spreadsheet into the attendance one described.
Linking is no problem, since it's the beginning of the year I'll just do a copy & Paste special.... paste link and they can still use that 2nd vacation spreadsheet to update both places for vacation time. However.... the vacation sheet has some extra info they don't need/want to see on the attendance one (ie. Lv @ 12:30, Holiday, etc.) How can I filter that out so that the only thing that appears is the V for Vacation that is linked & other attendance codes that are entered (S for Sick, etc).
I'm using Excel 2007
I am trying to find a way to quickly and efficiently filter an existing spreadsheet i have set up. My current spreadsheet is an excel sheet that has a couple thousand items and in the next column is the price for each item. My customers send me an email with a list of items ranging in anywhere between 1 and 1000 items requesting pricing. Is there anyway to quickly and efficiently filter the existing price list for the items my customers are requesting pricing?
Currently i have a filter on the item list but selecting each item is cumbersome and a waste of time.
I need to filter 20 out of 100 text values (3 letters each) in a table.
The 20 values are listed in a separate column and I don't know how to filter by that range without manually selecting all of those 20 in the filter?
I am trying to do is filter out one column of data with another. Basically if a value from column A is in column B... delete it.
Here is code that I am using:
Sub filter_out()
' filter_out Macro
' this will scan one column and remove cells from it that
' are in it that are in another column
Dim INV As Range
Set INV = ActiveSheet.Range("A2")
Dim MW As Range
Set MW = ActiveSheet.Range("B2")
I keep getting object errors and it fails...
i need to filter and delete duplicate values. where i have some issues is that i just need keep the first entry and delete the second (and so on) entry. means when i have a list with ids and hours like that:
id1 hours1
id1 hours2
id1 hours3
id2 ...
i need to have id1 and hours1 but delete hours2 and hours3. hours is the result of a formula. i tried with advanced filter but it somehow just hides me the duplicates and it sums me the hours.
I have been trying to transpose a piece of code that was put on this forum back in 2005 into my own Workbook but have been unsuccessful. The original piece of code filtered a column for unique entries and then filtered the main sheet for each unique entry and cut and paste each row that the unique value occurred in and in a new worksheet. A new worksheet would be created for each unique filter.
So what I am trying to do is look up a list of values that i have in a list on the worksheet. Then for each entry in my list, create a new page and cut and pasted the filtered main worksheet into that page.
Finally return to the main worksheet and then remove all filters.
I have attached the original file that was submitted so you can look at how it works and I have posted a sample my worksheet (with some things disabled to reduce file size but hopefully you will get the layout. However I don't currently have that sample on this computer. It is posted on another thread under the title "Selecting rows with specific values in a column".
I am currently using the following code below in the macros for filtering out a certain set of activities in fields LVL to be "0"
VB:
Dim PI As PivotItem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Lvl")
.PivotItems("0").Visible = True
[Code]....
I now wish to filter lvl activities with pivot items " 0" and "1" as well.
Column A
Row1 Orange-Apple-Pear
Row2 Orange-Grapes-Melon
Row3 Berry-Apple-Melon
Row4 Banana-Grapes-Melon
Row5 Orange-Grapes-Banana
Given the information above:
I want to find a macro that will auto filter on for example
"Banana" would filter to rows 4,5
"Orange" would filter to rows 1,2,5
"Apple" would filter to rows 1,3