Unique Value Function
Aug 28, 2009I know there is something I have to do to the worksheet before it will reconigze the function?
View 2 Replies
ADVERTISEMENT
Unique Ranks Function
May 10, 2007I have a list of data where some of the cell values are the same. I want to rank them uniquely but when I use the RANK function the cell values that are the same are given the equivalent rank. Is there any way of assigning the equivalent values a sequential ranking that is unique?
So in this example rather than producing the rank as this:
100 1
99 2
98 3
98 3
98 3
97 4
96 5
95 6
It would produce it as this:
100 1
99 2
98 3
98 4
98 5
97 6
96 7
95 8
IF Function For Last Occurring Unique Cases?
Mar 26, 2014I have an excel file that lists individuals who came into the clinic on a given day. I am interested in figuring out the amount of unique individuals in a given month/overall. As this is a working datasheet, I would like a formula that would automatically update this information for me. Here is an example of the file that I am working with: unique case example.xlsx
Specifically, I want an IF function that could place an 'x' in column E depending on if the name in column C is a unique instance. But if the name appears several times in the list, I would also like this function to place an 'x' in column E next to the last occurring unique instance. For example, the name 'Hanna D' occurs 3 times in the spreadsheet I have attached, but I want the 'x' to be next to the most recent incident, when she was screened, as opposed to when she was not contacted (missed/doc forgot).
I am not sure if you can even define a unique case in the IF function, let alone the last occurring unique case.
I am not interested in conditional formatting (highlighting duplicate cases and then placing an 'x' next to the most recent highlighted case) because my spreadsheet has 300+ individuals and it is very time consuming to manually move the 'x' when the same individual appears at a later time.
Creating Unique ID Using Concatenate Function
Mar 26, 2014I need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.
View 3 Replies View RelatedCounting Unique Values With A Function
Feb 16, 2006I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements,
View 14 Replies View RelatedFunction For Unique Values In Array?
Oct 4, 2011I have an array called "Universe", which have duplicated values. I am trying to write a code that gives me an array which have only unique values.
View 9 Replies View RelatedFunction For Counting Unique Values?
Jan 31, 2013what function can I use to count the number of unique values in a range of cells?
View 4 Replies View RelatedCount Unique Results: Function <> Filter
Dec 27, 2007Data: four variables A,B,C and D from A2:D6000
I want a count of unique D values
when A=xxx
and B=yyy
and C=zzz
I have named the A,B,C,D ranges as RangeA, RangeB, RangeC, RangeD
I have used the function
=SUM((RangeA="xxx")*
(RangeB="yyy")*
(RangeC="zzz")*
But what do I use to count-unique values of variable D ?
=SUM(IF(FREQUENCY(RangeD,RangeD)>0,1)) doesn't work.
=SUM(1/COUNTIF(RangeD,RangeD&"")) doesn't work either
They don't work because D can occur during different combinations of A,B and C. In other words, the D value of 'Smith' can, and does, occur when A is 1,2 or 3 and additionally when B is Pass, Fail, Withdrawn.
They "don't work" because I can use filters on A, B, C and D to see what the correct answer should be .. and the function doesn't return the correct answer.
I hope I've explained this ok - I've been searching on several excel-help websites for 2 days now and one of the days my client is going to want some results.
Count Unique Text Cells Ending In X - Function Only
Nov 15, 2013I have some data in a column, starting at row 6 that I'd like to find the number of unique text values for. For this I have been using this formula
Code:
{=SUM(IF(FREQUENCY(IF(LEN(A6:A10000)>0,MATCH(A6:A10000,A6:A10000,0),""), IF(LEN(A6:A10000)>0,MATCH(A6:A10000,A6:A10000,0),""))>0,1))}
The data is dynamic so I picked an arbitrarily large number (10000) and the above formula successfully ignores blank fields.
However, I'd like to now find the count of unique text values that end in X. For example, let's say the data are as follows
Dog_Cat
Mouse_Dog
Mouse_Cat
Mouse_Cat
Mouse_Cat
Mouse_Mouse
Elephant_Elephant
How would I go about (in one function) finding the unique values that end in "_Cat", in this case 2 ("Mouse_Cat" and "Dog_Cat")?
VLookup Function - Copy Values Corresponding To Unique Identifier From One Sheet To Another
Jul 8, 2014I would like to copy values corresponding to a unique identifier from one sheet to another, using the vlookup function. The problem is that after a certain cell the values are given as N/A even though they are present in the sheet I want to copy them from.
View 3 Replies View RelatedFunction / Formula In Excel To Count Unique Values Matching Criteria
Jun 4, 2013I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.
Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return
Copy And Paste Unique Values From Column And Use Countif Function To Calculate Average
Jan 19, 2013I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.
On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.
I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).
Sheet 2 contains the output, in the format I'd like to see it.
Sheet 2:
Supervisor Name
October
November
December
Supervisor 1
8.3%
[Code] .......
Count All Unique Values (text) + Definition Of UNIQUE :D
Aug 24, 2009If 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 RelatedAssign Unique ID IF Adjacent Cell Has Unique Value?
Jun 5, 2014I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:
0001: Apple
0001: Apple
0002: Google
0003: Sony
0003: Sony
0003: Sony
0003: Sony
0004: Ford
0004: Ford
0004: Ford
.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.
# Of Unique Dates Per Unique List Entry
Oct 22, 2009Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
Unique List Would Grab All Unique Entries
Jul 30, 2009I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
Identifying Unique Entries Based On Unique Entries In Another Column?
May 29, 2014I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
Remove All But One Non-unique Line Of Data, While Leaving Unique Data Alone
Jun 10, 2007I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.
The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:
a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or
b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)
example:
Data loaded into the sheet before the macro runs:
Row: Date:
1 06/09/07 *
2 06/10/07 *
3 06/11/07 *
4 06/09/07
5 06/10/07
6 06/12/07 *
7 06/13/07 *
8 06/09/07
9 06/11/07
10 06/14/07 *
* The rows with asterisks are the ones that I would like to have pulled to the second set of data (below)
Data in the sheet after the macro runs:
Row: Date:
1 06/09/07
2 06/10/07
3 06/11/07
4 06/12/07
5 06/13/07
6 06/14/07
Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.
Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending
Oct 5, 2007I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
ex) 1.751
2.52
3.753
418
417
416
Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function
Apr 8, 2014I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
Inserting An INDEX,MATCH Function Into A HYPERLINK Function Instead Of Cell Reference
Mar 20, 2009Another interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedFunction Returns Value To Cell As Single - Function Is Defined To Return Double
May 14, 2013Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
Apr 13, 2007In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedCalling Function That Acts On Matrix Range Within Another Function
Jul 23, 2013I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
Nesting Index / Match Function Within Vlookup Function?
Dec 3, 2013let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
Function To Mirroring Cell Relative To Function's Location
Jul 17, 2008I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
Nested IF Function Error (entered Too Many Arguments For This Function)
Nov 25, 2008I keep getting the "You've entered too many arguments for this function" error.
Here is the formula:
=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))
Nesting Ceiling Function In IF Function - Conditional Formatting?
May 13, 2013I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
Force Function To Use Row Relative To Cell Housing Function
Mar 2, 2008I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).
My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.
My Script:
Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult
Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select
If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If
End Function
So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).
My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
2007 Right Function With Embeded Find Function
Mar 26, 2009I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....