Returning Distinct Values From A Table?
Jul 30, 2014
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.
View 2 Replies
ADVERTISEMENT
Sep 12, 2012
I have two tables on two different worksheets. I would like to automatically populate a distinct list into column A in table 2 from the data in column A in table 1.
Table 2 must auto populate from table 2 every time a new row is added into table 1 - without any extra user interaction e.g. pressing a button etc. No offense intended to my better half but if she has to do anything more than enter a new row in table 1 she'll likely manage to screw it up after a week
I kind of managed to get it working using the below formula but both sets of data must be in tables so I can't copy the formula down the column without creating redundant table rows.
=INDEX(Classes!$A$2:$A$999, MATCH(0, COUNTIF($A$1:A1, Classes!$A$2:$A$9), 0))
Example
Sheet 1 = "Classes"
Column A = "Student Names"
John
Mike
Paul
Andrew
Paul
Paul
Mike
Sheet 2 = "Total Due"
Column A = "Student Names"
Paul
Mike
View 3 Replies
View Related
Jan 21, 2014
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
View 9 Replies
View Related
Sep 18, 2012
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.
View 1 Replies
View Related
Apr 18, 2013
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")
View 4 Replies
View Related
Dec 23, 2006
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 Related
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
Apr 7, 2008
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 !
View 9 Replies
View Related
Jan 2, 2010
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.
View 9 Replies
View Related
Nov 26, 2012
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
View 6 Replies
View Related
Jan 11, 2013
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,
View 1 Replies
View Related
Dec 24, 2013
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]
View 5 Replies
View Related
Dec 5, 2013
I am trying to basically search for the last non zero entry in a column and then return a value in the same row as that last non zero entry.
For example:
My first column of the table is time, second column is submission/marking of coursework (denoted by +1 & -1) and the third column is the number of courseworks awaiting to be marked. My aim is to search the third column for the last non zero value then return the time (from first column) that last non zero value appears.
View 6 Replies
View Related
Mar 5, 2014
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
View 2 Replies
View Related
Mar 27, 2014
I have a list of projects and a list of milestones and I want to extract every milestone for a project one by one. I have found a way to do this for one project but as there is not a fixed number of milestones, it's proving tricky to do it for multiple projects as I don't know how to tell the formula to stop looking at the Project 1 and move on to Project 3 for example.
This is a very very simplified version of my table (there are other columns inbetween and many more projects)
Project 1Milestone 1
Project 1Milestone 2
Project 1Milestone 3
Project 1Milestone 4
Project 1Milestone 5
Project 2Milestone 1
Project 2Milestone 2
Project 2Milestone 3
Project 2Milestone 4
Project 2Milestone 5
Project 2Milestone 6
Project 2Milestone 7
Project 3 Milestone 1
Project 3 Milestone 2
View 2 Replies
View Related
Apr 14, 2014
I am looking to return text based on the maximum value in a table.
Dog
2
Cat
10
Horse
4
Fish
27
Lion
1
Let's call the column with the animals over it "A" and the one with the numbers "B", and the rows are 1-5 to make explanations easier
I am looking to have a formula return the name of the animal with the highest three numbers (fish, cat, horse), but don't need the numbers. I am pretty confident this would be an index match formula, but I'm not too familiar with those functions.
View 1 Replies
View Related
Apr 26, 2013
I have a pretty large table,(153,000 rows) My lookup_value is a 7 digit number. If my lookup_value isn't in the lookup_vector, it should return "#N/A" (which it does most of the time) but I noticed it was returning a result when the lookup_value didn't exist...it didn't exist anywhere in the table.
View 3 Replies
View Related
Nov 10, 2009
I need to retrieve information from multiple rows in a table based on certain criteria (date compared to today's date, existence of "no" in a column).
View 6 Replies
View Related
Jun 23, 2008
The code below (ran from Excel vba) deletes all data from an Access table, paste data from Excel into another table, and then run two Access Macro's and paste data from yet another table back into Excel.
My problem is that when I run the query from Excel, the data returned to Excel is always zero. When I step through the code, and check the Access database, all the steps are carried out properly. E.g, the delete query is deleting all the records in the correct table, the append query is copying the correct data into the correct table in Access. The two macros do run, but then when the resulting table is returned to Excel, it brings back zero's.
Doing all this manually in Access is returning the data correctly.
The two Access macros are running some queries, E.g. the "001 UpdateLatestData" is running two queries and the "002 CreateMonthlyResults" is running about 15 separte queries.
Sub OPENACCESSTABLE_DELETE_ROWS1()
Dim db As Object
Dim accApp As Object
Access_DB = ActiveWorkbook.Worksheets("MACRO").Range("C1").Value
DBPath = "C:MinetteProperty.mdb"
Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase DBPath
'Delete Query
DoCmd.RunSQL "DELETE [001 non motor data].* FROM [001 non motor data]"
Set accApp = GetObject(Access_DB) .........................
View 9 Replies
View Related
May 11, 2014
I am building a table that assigns three roles to chores: Accountable, Helper1, and Helper2. I have a "user interface" tab, where there is a dropdown list where particular chores can be selected. I am looking to have the people in each role for the selected chore returned into the cells. I have attached an example file to clarification. For example, I am looking to have the name (Mike, Shannon, Tom, Blake, Kelsey, Sam) from tab #2 populated into the related cells C3, D3, and E3 on tab#1. I think it's possible with a long imbedded IF function, but I am looking for a more simple solution if possible.
Excel_Help3.xlsm.xlsx
View 6 Replies
View Related
Aug 15, 2008
I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.
What I want to end up with, is which postcode the incidents are in!
So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.
Here's what it looks like!
Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581
Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444
I want to end up with
Incident Postcode
01 SP7185H
etc etc.
View 9 Replies
View Related
Aug 15, 2008
I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.
What I want to end up with, is which postcode the incidents are in!
So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.
Here's what it looks like!
Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581
Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444
I want to end up with
Incident Postcode
01 SP7185H
etc etc.
View 9 Replies
View Related
Aug 13, 2009
I have used several IF and AND functions in the past in order to check to see if 2 values = true and return a third.
(for example =IF(AND(A1>10,B1<20),C1,"")
However I can't seem to figure out the senario below.
I have 2 coulmns, Column A has dates and Column B has a numeric value
I need to check to see if column A falls between 2 dates and returns the sum of column B
For example if have 100 rows and 10 of those rows have dates that fall between 06/01/09 and 07/01/09 then add up column b for those 10 rows and return the value.
View 3 Replies
View Related
Sep 7, 2009
i have a formula that looks up data from a pivot table....the pivot relates to a data request log and a pricing request log - both separate. the data request log and pricing request log includes data and pricing activities....some are only data, some only pricing, a few are both - each activity has its own unique request number - if both data and pricing then the request number will be the same
so the pivot looks up a request number eg F1.....it counts how many actions there are for this request number (eg request F1 may have 6 actions) and displays this number. eg F1 data only request....6 actions.....no pricing activities/request...the pivot will display 6 under the column Data but then returns the value #N/A under the column pricing if no actions/request found for pricing...i want this to return "blank" if there are no actions/request found under either pricing or data. the formula used is as follows:
under the column named data:
=VLOOKUP(J7,'DATA PIVOT'!A7:D150,3,FALSE)
under the column named pricing:
=VLOOKUP(J7,'PRICING PIVOT'!A7:D150,3,FALSE)
i think that because FALSE is used this returns the value #N/A?
View 3 Replies
View Related
Sep 26, 2005
Returning Descending Values
I currently use the following to find the max number is a column:
=MAX($P$4:$P$65536). If I wanted to find the second highest, then the third highest, etc.. how would this be written?
View 14 Replies
View Related
May 11, 2009
it seems that excel is deciding what type of array this is by evaluating the type of the first cell in each column and applying this type to the rest of the array's column. Problem is that i want all values to be brought in, text and numerical.
Dim VArray As Variant
Sub DA201()
If FileThere(Path & "DA201.xls") Then
GetDataTowers Path & "DA201.XLS", "", "DataRange", Sheets _("Towers").Range("CStart"), False, False
Else
MsgBox ("File DA201.xls not found. Click OK to continue")
End If
End Sub
Public Sub GetDataTowers(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim n As Integer
Dim row, m As Integer
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else....................................
View 9 Replies
View Related
May 24, 2007
I have been able to get the following code to work (which simply returns the start of data on a sheet):
Sub FindDataStart( )
Dim lRow As Long
Dim lColumn As Integer
For lRow = 1 To Rows.Count
For lColumn = 1 To Columns.Count
If Trim( Cells(lRow, lColumn).Value) <> "" Then
Goto EXIST
Else
End If
Next lColumn
Next lRow
EXIST:
Exit Sub
End Sub
Now I want to build on what I have learned so far and from another Sub, I want to call the FindDataStart sub and have it return the row and column # (from where the data starts).
I started by making the following changes to the FindDataStart sub but got stuck
Changed :
Sub FindDataStart( )
To:
Sub FindDataStart( RowNumb As Long, ColNumb As Integer)
I also added the following line to the end of the FindDataStart sub
FindDataStart = (lRow, lColumn)
View 9 Replies
View Related
May 12, 2014
In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).
In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.
The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.
Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.
So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.
View 3 Replies
View Related
Aug 7, 2014
I have a table of data-
IDDescriptionDatePax Count
316COACH01/07/20135
323COACH3/07/201410
324COACH18/07/20141
325COACH20/08/201420
333COACH21/09/201445
345COACH22/09/20143
351COACH23/09/20143
362COACH21/10/20142
371COACH22/11/20142
394COACH23/12/20140
416COACH23/12/201418
422COACH23/12/201439
Example, the real data is about 38,000 entries. I want to build a query to return the value of Column D (pax count) based on the values of Columns A (ID) and C (Date)
This would feed into a second, ID specific table, like this-
ID 536 Bookings JULY 2013
1/07/20132/07/20133/07/20134/07/20135/07/20136/07/20137/07/2013
(value) (value) (value) (value) (value) (value) (value)
SUMPRODUCT, usually my mainstay, is for some reason letting me down, returning a value of 0 for everything irrespective.
View 9 Replies
View Related
Aug 16, 2014
How the following problem gets sorted out
ColumnAColumnB ColumnEColumnF
33rohan55
44tata55
55dev44
55rony33
As you can see from above i want to lookup all the values in column E in column A and return the corresponding value in column F.
View 4 Replies
View Related
