Unique Count Of Range - Vba Coding

Feb 22, 2007

how to count unique records, and I can only find formulas, and not code. This code works fine, and I get the right "actual" count, but I need to change it to a unique values count.


Sub CountEmployees()
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 2 Step -1
If Cells(t, 8).Value <> "" And Cells(t, 8).Offset(2, 4).Value <> "" Then
Cells(t, 12).Select
Selection.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
List = Selection.Cells.Count ' Need to make this unique count
Cells(t, 9).Value = List
End If
Next t
End Sub

View 9 Replies


ADVERTISEMENT

Count Unique Entries In A Range

Sep 5, 2007

count unique entries in the Range A1:A10

i have data which repeats but i ant to count only unique entries?

View 9 Replies View Related

Count Unique Value In Range Of Cell With Given Condition?

May 28, 2014

I would like to check if there's a way to count the number of unique value with a given condition

Eg. condition where parcel count <> 0, also want to find out the unique value of district and postal sector

Attached is the files, and my desired result is highlighted in RED.

View 3 Replies View Related

Count Unique Records In A Filtered Range

Jan 21, 2009

Is it possible to count the unique entries in a range based on the results of a filter that has been applied? I basically have a column with 2000+ cells that contain some matching values and I only want to count the unique entries. This will need to be a dynamic count as well as the filter criteria can and will change all the time.

View 14 Replies View Related

Count Unique Items In Filtered Range

Dec 13, 2009

As the subjects states I need to count the unique entries in a filtered range.

View 7 Replies View Related

Count Unique When Multiple Criteria In One Range

May 17, 2012

I have two ranges of data and want to count the unique number of times the values in column A occur if they have two values in column B. For Example:

ColA ColB
333310143333306344441014444430635555161246666101466661612466663063

I want to count the number of times value in ColA has the value 1014 AND 3063. My intention is to create a table that cross references the two

10143063161241014331306333116124112

I searched and found answers where multi criteria were in multi columns but I can't seem to find a solution to this means of find the unique count in ColA.

View 3 Replies View Related

Total Count Of Unique Digits In A Range

Jun 24, 2013

I would like a formula to count the number of unique digits from a range of cells.

For example...

Range A1:F1...
11 23 36 47 48 49 = 8
Range A2:F2...
1 11 12 21 30 31 = 4 etc

View 5 Replies View Related

Count Unique Entries Within Variable Date Range ..

Apr 27, 2009

I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.

View 2 Replies View Related

Count Unique Entries Within Variable Date Range

Jan 26, 2008

Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.

Goal: create a count of unique entries within a defined variable date range

I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.

I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.

It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?

View 9 Replies View Related

Count Unique Text Entries Given A Certain Condition In A Separate Range?

Aug 11, 2014

I need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.

Company Name
Company Type

Company A
SME

Company B
SME

(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.

View 5 Replies View Related

Count Unique Values / Text Within Range Of Cells That Contain Duplicates / Blanks And Errors

Jun 25, 2014

How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?

For e.g., in Column A (row 1 - 10):
Proj-001
Proj-001
Proj-002

Proj-004
#N/A
#N/A

Proj-007
Proj-002

View 3 Replies View Related

Count All Unique Values (text) + Definition Of UNIQUE :D

Aug 24, 2009

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

Dynamic Range In Vba Coding

Nov 2, 2008

I'm trying to write a code which would place formulas in the row just below the my range (normally a matrix of figures) to sum the column in that just above that. Here's what I've got so far but it doesn't work.

View 6 Replies View Related

Avoid Hard Coding Range Reference

Oct 5, 2006

I now can open all the workbooks in a filefolder and look at each of the sheets in each file and build an array to consolidate a range from one sheet to another. The problem is that the one range is hardcoded in the script (because that is what I was using to test.) I have written code to build an array of 66 rows and 5 columns that are stored in a worksheet in a named range.

how arrays work so I can use both of these arrays - one array ConsolParams(), to identify the range in the Sources part of the consolidate statement. The other array MyArray() stores the sheet names. Actually ConsolParams also stores "to" range now hardcoded as "J11".

Option Explicit

Private Sub cbConsolidateToRollups_Click()
Dim MyArray() As Variant
Dim Source As Variant
Dim sToPath As String
Dim sRollUpWB As String
Dim sRollUpToWs As String
Dim sRollUpFromWS As String
Dim sShtName As String
Dim sSourceWS As String
Dim nShts As Integer
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim WB As Workbook
Dim i As Integer
Dim r, c, p As Integer
Dim ConsolParams(5) As Variant
Dim CostType, RangeName, FromRange, TargetCell, TargetReport
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False.......................

View 6 Replies View Related

Mixing Range And Cell References When Coding FORMULA

May 20, 2013

This works

ws2.Range("C5").Formula = "=sum(D5:ZZ5)"

guide me on the syntax when the final column reference is a variable

ws2.Range("C5").Formula = "=sum(D5:" & Cells(5, l_LastCol) & ")"

doesn't work

View 3 Replies View Related

VBA Unique Value Count

Apr 1, 2009

I have the below code, in a cell but I am trying to input this into a macro, so the only thing that shows in the cell is the formula.

The formula I have in the cell is:

View 14 Replies View Related

Unique Value Count In VBA

Jul 15, 2009

I have this formula that works perfectly fine for what I am trying to do (from another post) but I am wanting to transfer this into VBA. It is an array formula so I'm not sure if this can be done.

How can I convert this, into R1C1 code or any way possible. I would put it within a WITH RANGE statement.

View 9 Replies View Related

Count Unique

Apr 25, 2009

aaa.xls

how to count lines in the same order

for exp for this attachment file is 7 lines

View 13 Replies View Related

Formula For Unique Count

Jan 20, 2013

I am attaching the sheet : Uniquecount.xls

Need to do this using formulas and without needing to add any new columns.

Need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab.

Want the formula to be applied to the summary to go and lookup/count from the other sheet.

Have shown the expected results in the col "total unique heads".

View 1 Replies View Related

Formula For Count Unique Value

Feb 27, 2013

Any formula to count only unique values in particular column

E.g.

1
2
3
1
5
1
6
1

answer should be 5

View 3 Replies View Related

How To Count Unique Rows

Apr 7, 2014

Col A Col B
163401 1
163401 1
163401 0
163402 1
163402 0
163402 0

GOAL -I want to be able to count Col B only once for the same set of records in COL A.

Based on Distinct criteria on COL A, I need to be able to count COL B. The count should be 1 for 163401 and not 2. Similarly for 163402 the count should be 1

View 11 Replies View Related

How To Count Only Unique Values

Jul 24, 2014

I'm working with a data set (attached) that tracks financials at both the job and item level (in my customer's data - a unique job can be made up of a single or multiple items).

I've successfully built a formula (in column C of the attached) that counts the total number of items and their associated $ values that were invoiced in a given month based on my customer's requested item-type categorizations (i.e. "Exact Match", "Similar", "Inferred", "Cost-Plus").

The problem I'm running into is building a formula that counts the first unique instance of the Job ID associated with those items. For example - in cell C33 on the attached - The total number of items invoiced in June that were "Exact Match" was 10. These items were spread across 9 unique jobs - and that's where I'm struggling - to build a formula that only counts the first unique instance of the corresponding Job ID in Column C.

The formula I'm having trouble with is located in Cell D12 on the "Dashboard" tab of the attached.

The formula as I have it is returning a result of 8 - because it's summing the "1"'s in Column I on my "Invoice Data" tab - however it should read 9 (Filter Column Q on the "Invoice Data" tab to "June", Filter Column AQ on the "Invoice Data" tab to "Exact Match", and then sum the unique job IDs in Column C).

Excel_Forum_Example_Dataset_7.24.14.xlsx‎

View 1 Replies View Related

Pivot - Unique Count

Jun 14, 2009

I want to have a unique count field in my pivot table. I already read (via forum search) that this can only be achieved by adding an extra column to the database.

The problem is: I have no clue which formula to use for this column. Please have a look at the attached picture. Can someone provide me with a formula for this example?

View 14 Replies View Related

Count Unique Values ....

Dec 28, 2009

So here is my dilemma and I haven't been able to find anything to exacly help me out:

I have a large (100k+ records) sheet of sales invoice details.

I need to add a total item line per invoice number to each record ....

View 14 Replies View Related

Count Unique Values

Jan 13, 2010

Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274

I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.

View 14 Replies View Related

Count Unique With Two Variables

Apr 23, 2009

I am trying to find a formula that will count the number of unique occurences with two variables (Department and Period).

Since a person can be listed in the department more than once for each period, I need to find the unique number for each department by period. I have attached a simple file as an example. Any formula would go in the yellow area.

View 2 Replies View Related

3 Variable Unique Count

May 20, 2009

I am trying to find the number of unique 3 variable combinations there are in my rows of data. I have tried the frequency function but must be missing something.

Attached is a sample of what I am trying to achieve although there may be a better way of portraying the data.

View 4 Replies View Related

Count Unique Formula ?

Nov 30, 2006

I have a column with names and I would like to count how many names there are in this columns. This is easy but in fact if there is twice the same name the formula will count it twice and I don't want that. Is there a solution to count only the number of persons present in this column without counting twice the same person??

View 12 Replies View Related

How To Count Only Unique Values

Jun 6, 2014

I want to count data that corresponds to 4 criteria for data located in Columns B, C, D and E. However, I only want to count only the unique values in Column A. How do I improve on this formula?

=SUMPRODUCT(($E$2:$E$5=J$2)*($D$2:$D$5=I$2)*($C$2:$C$5=H$2)*($B$2:$B$5=G$2))

See data below

View 4 Replies View Related

COUNT THE UNIQUE NUMBERS

Feb 8, 2007

I have a column full of Invoice No's. I want to count the number of Invoices that are unique, ir avoiding duplicates.

I know I can use the Advanced filter facility but I'd rather do it in a formula if possible.

View 9 Replies View Related







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