VB Code To List Distinct Values + Associates

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


ADVERTISEMENT

How To Automatically List Down Associates If Workflow Is Mentioned In Column

Mar 14, 2014

How to automatically list down associates if workflow is mentioned in the column? Like in attached sheet, in column B workflow is listed down and in column C name of associates should get populated in sequential manner from column F, G and H. For example A, B, C and D is aligned to workflow X (Treating this as primary source), whenever in column B, workflow X is populated (manually) it should list down list of associates in order - A then B, then C, then D; once cycle completes, it should repeat again A, B, C, and D.

View 1 Replies View Related

Distinct List Of ID Numbers From Several Sheets

Jul 22, 2014

I have several sheets in one workbook. For ease, the sheets are labeled Customer1, Customer2, Customer3 etc. Within these sheets are 5 columns of data, with unique headings that are consistent for all Sheets.

In Column A of each Customer sheet, is the Product ID number. I would like a distinct list of all Product ID numbers in the workbook, across all Sheets. (The amount of data in each sheet may increase or decrease)

View 7 Replies View Related

Create Distinct Unique List For 2 Columns

Aug 6, 2014

So far I created code that creates a Distinct Unique item's list for one column. It works as it should but I want to modify the code to create a Distinct Unique item's list for two columns that are related to another i.e. a person's name to their license #.

Here is the code below that works for one column only:

[Code] ......

View 9 Replies View Related

Auto Populate Distinct List From One To Another Table

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

Create Distinct List And Sort Alphabetically?

Jul 16, 2013

I am trying to create an alphabetised unique list for a report from an Excel master file and have searched the internet for solutions which all only seem to half work for me. I've copied a couple of array formulae below to try what I'm after and what the results are. 'RefSource' is the name I've given to the column of data I'm trying to sort.

{=INDEX(RefSource, MATCH(0, COUNTIF($A$2:A2, RefSource),0))}

This one works when I copy the formula down and gives me the unique list but doesn't sort the it into alphabetical order.

{=INDEX(RefSource, MATCH(MIN(IF(COUNTIF($P$1:P1, List)=0, 1, MAX((COUNTIF(RefSource, "

View 1 Replies View Related

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 View Related

How To Count Distinct Values In A Column

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

Distinct Values From 2-dimensional Range?

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

Counting Based On Two Distinct Values

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

Formula: Count Distinct Values

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

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

SUBTOTAL IF Formula To Sum Distinct Values In A Column

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

Extract Unique Distinct List From Two Columns With Array Formula And Sort From A To Z

May 30, 2014

I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:

List1
List2

Final List

Audi
Ford

[Code] ....

So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.

{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}

View 2 Replies View Related

Create Helper Column That Marks Distinct Values

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

Auto-populate Combobox - Extracts Unique Distinct Values From Column

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

Summing Up Values In Column Corresponding To Unique Distinct Values In Another Column?

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

Excel 2010 :: Count Distinct Text Values In One Column If Another Column Equals A Value

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

VBA Code To Get Individual Values In A List Placed Into Individual Cells

Apr 7, 2014

My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...

Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..

When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of

Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x

I had

Code:
Combobox1.list = x

And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).

View 2 Replies View Related

Take My Area Code List, And Make A State List.

Jul 28, 2009

Basically I have a giant list (50,000+) of area codes (like 312, 216, 517, etc...) and I would like to make a new column right next to that, which takes those area codes and transcribes them into states so it would be (IL, OH, MI, etc...)

I have a table on the 2nd page of my excel workbook which has every single area code listed in column A, and in column B it has all the state symbols. I got it from here: .....

View 9 Replies View Related

To Take My Area Code List, And Make A State List

Jul 28, 2009

Basically I have a giant list (50,000+) of area codes (like 312, 216, 517, etc...) and I would like to make a new column right next to that, which takes those area codes and transcribes them into states so it would be (IL, OH, MI, etc...)

I have a table on the 2nd page of my excel workbook which has every single area code listed in column A, and in column B it has all the state symbols. I got it from here: [url]

How would I go about making this new column on page 1 of my workbook which takes all the area codes I have and makes a brand new list of the states by looking up the area code/state table I have on page 2 of the workbook?

View 9 Replies View Related

Getting Distinct Counts From Multiple Columns?

Sep 28, 2011

How to calculate a count of unique records across two columns (customer number and acct number) with out much luck. For example, a customer number may appear 3 times but be associated with 3 separate acct numbers.

I've found a lot of in-depth formulas online but I'm not sure if they're what I need. I think I need some sort of array formula but I'm a little stuck.

View 9 Replies View Related

Distinct Count In Variable Range?

Apr 3, 2013

I have a workbook with multiple sheets. On each sheet is different sets of sales person data. Each set consists of a sales order number, and the type of product that was sold.

I need a count of all the distinct product types that each sales person sold.

I need the solution in VBA, as i need to run the macro through all the worksheets. The size of the range for each sales person differs.

I have got all the VBA done, except for the part where i specify the FORMULAR1C1 for the specific cell, to do the distinct count.

View 1 Replies View Related

ADO Select Distinct Multiple Columns

Aug 5, 2009

I am using ADO to select data from a CSV file, column 1 has some duplicate Incident numbers, but the rest of the columns the data may be different but not interested in that so I cannot use SELECT DISTINCT incident, desciption as this applies to all columns

so I tried the following

StrSQL = "SELECT DT.IncidentNumber, ST.Description -
FROM (SELECT DISTINCT IncidentNumber FROM " & StrFile & " ) DT -
INNER JOIN " & StrFile & " ST ON DT.IncidentNumber = ST.IncidentNumber"

however it returns ALL rows, if I use just

SELECT DISTINCT IncidentNumber FROM " & StrFile

I get the rows I am interested in but not the extra data I require

View 9 Replies View Related

Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...

View 11 Replies View Related

Counting Unique / Distinct Occurrences With Wildcard

Aug 5, 2014

I'm working with a set of text values. The sets of text can be either 2 or 3 chars long (in this example 2), and are separated with a pipe. The end char for each set of chars in fixed. In this case a Z.

I'm looking for a formula which counts the unique / distict number of sets of chars. I have been trying to use COUNTIF with a wildcard (?Z), but unfortunately I haven't been able to find the correct formula yet.

For example.

|AZ|AZ|AZ|BZ|AZ|AZ|BZ|AZ|BZ|AZ| -> 2 (AZ, BZ)
|AZ|AZ|AZ|BZ|CZ|AZ|CZ|AZ|BZ|AZ| -> 3 (AZ, BZ, CZ)
|CZ|BZ|AZ|BZ|CZ|AZ|EZ|AZ|FZ|AZ| -> 5 (AZ, BZ, CZ, EZ, FZ)
|BZ|BZ|AZ|BZ|CZ|AZ|EZ|AZ|GZ|AZ| -> 5 (AZ, BZ, CZ, EZ, GZ)

View 9 Replies View Related

Identify Distinct Start Stop Times?

Aug 21, 2014

I am attempting to create a datasheet to track the actual time an agent takes a break or lunch. Here is an example of the data that I am using.

name Date Lunch Sch Brk1 Sch Brk 2 Actual Time
BrandonHolt8/19/201411:10 8:30 1:20 8:24:20 Break
BrandonHolt8/19/201411:10 8:30 1:20 11:42:34 Break
BrandonHolt8/19/201411:10 8:30 1:20 12:57:46 Lunch

Because I have two break times, possibility of more depending on the agent, how do i create a formula that will look at this data, take the schedule time for say break one and only apply that against the time that is more in line with the closest actual time?

View 1 Replies View Related

Distinct Colours In A Cell And Conditional Formatting

Feb 8, 2010

This question about conditional formatting appears to be easy. Lets say I have a column like the following, like I posted somewhere else: (not very relevant for this)

BGW
3UG
WWU
1BR
UUBRG
WUBRG
7UUGGRRR

How do I have all characters in this column be coloured accordingly to:

W = WHITE
U = BLUE
B = BLACK
R = RED
G = GREEN

So that 7UUGGRRR would automatically switch to:
7UUGGRRR

What I already tried:

- I applied conditional formatting to the column (actually, the cells interval, [C2..C8]) creating the rule: 'format only cells that contain -> specific text' -> then I added "U" as the text and told it to colour blue. As I predicted, this didn't work; the entire cell containing a single "U" will get blue, which is unwanted.

View 9 Replies View Related

Counting Rows Based On 2 Columns (Distinct)

Mar 19, 2007

From a base table, I need to create a new table listing only the unique combination of 2 columns and their individual sum. Example:

Base Table:

ITEM-----Loc-----QTY
Pen------School----5
Pencil----Office-----9
Binder---Office-----9
Pen------Office-----6
Pen------School---15
Binder---School---12
Pencil----Office----10
Binder---School----6
Pen------Office----11

I need to have a table resulting to the following:

Item-----Loc------Qty
Pen------School----20
Pencil----Office-----19
Binder---Office------9
Pen------Office-----17
Binder---School----18

View 9 Replies View Related

Distinct Count Of Range Using A Lookup Criteria

Feb 9, 2009

I have a problem getting an adequate formula for the following.

"Sheet 1"
Column A.............Column B
Employee Code.....Distinct Count
ZZADW...............Formula Result
ZZALM................Formula Result
ZZALS................Formula Result
ZZANG................Formula Result

"Sheet 2"
Column A............Column B
Employee Code.....Account Number
ZZADW...............1SSS
ZZADW...............1STT
ZZALM................5GHL''''''''

View 9 Replies View Related







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