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


ADVERTISEMENT

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 Values In A Filtered Column

Dec 29, 2008

I need to count the number of unique names from a column of filtered text. On the attached example D5 is where I need the value, the current array I have does not work when the filter is applied by changing the product in drop box. I need to have a count of the unique account names from column A.

View 3 Replies View Related

Count Unique Records For A Specific Date

Feb 19, 2010

I have a table in a sheet and I want a formula that will count the unique records. The simplified database is as follows (in a table):

View 6 Replies View Related

Count Unique Records With Criteria From Another Column

Feb 14, 2008

As the title says, I need to count the number of unique records (names) in column A, where column L is = to something specific (X,Y,Z,W, whatever) for some statistics im trying to report.

Please see the attached spreadsheet. For role X statistics, I need a count of the unique names from col A, where col L = X. Based on my sample spreadsheet, the number should be 2. For Role Y, it should be 3.

There is the potential for spaces in the rows, and no one will be 'cross role'

I've got a few different methods to just count unique values with specific criteria in the same column, but I just can't make anything work for specific criteria in another column.

View 9 Replies View Related

Formula To Count Unique Records In A Column

Apr 30, 2009

I need a formula that will:

Count unique records in column C
Where value in column N = "ABC"
And value in column I = "XYX"

View 9 Replies View Related

Count Unique Records With Multiple Criteria With Formula

Sep 18, 2006

Count unique records in Column B where.

1.)
... Column H >=A1 and <=A2

2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2

3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2

4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""

A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
Column B - 6 digit number (or blank)
Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)

View 7 Replies View Related

Count Unique Values Based On Duplicate Status In Another Column On Filtered Data

Mar 5, 2014

I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).

What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.

Picture:
Column C Column D
111111 M
111111 M
111111 M

[Code]....

Currently calculates: M=9, F=2

Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.

Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))

View 9 Replies View Related

Count The No. Of Occurrence Of Certain Value If The Range Is Filtered?

Nov 10, 2008

Countif can be used to count the no. of occurrence of a certain value within a range. However, if the range is being filtered, can the no. of occurrence be counted?

View 3 Replies View Related

Count Rows In Filtered Range

Jun 28, 2006

How do I know how many rows are selected by rngToFilter? I need to add in a conditional statement if its 0.

For Each rngCell In rngUnique
sheetName = rngCell.Value
ThisWorkbook.Worksheets(sheetName).Delete
rngToFilter. AutoFilter Field:=4, Criteria1:=rngCell.Value
rngToFilter.AutoFilter Field:=28, Criteria1:="="
Set rngFiltered = rngToFilter.SpecialCells(xlCellTypeVisible)
Worksheets.Add
rngFiltered.Copy ActiveSheet. Range("A1")

View 6 Replies View Related

Count Records Within Specified Time Range?

Jan 16, 2013

I need to count the number of records having hospital admissions within one year prior to the index admission. The list of all admissions is in one worksheet, the index admission in another worksheet. They can be matched on ID number.

View 3 Replies View Related

Count Each Item In Filtered Range With Duplicates

Nov 20, 2006

Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...

eg

12345325
12345325
435ghfdhy
5464OKff
SEDDONF4

[Code]...

As we can see here there are 14 lines of data but only 7 make up the dataset

so if X was the variable assigned to this it would = 7

Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory???

ERROR#9 OUT:

View 9 Replies View Related

Count Of Visible Rows In Filtered Range

Jul 16, 2014

From a combobox selection i filter a table for all entries containing the selected ID (from the combobox).

I first wanted to use selected columns from the resultant display (the filtered table) to populate another combobox so the user could drill down to the final selection that way, but seeing the mess i was getting involved in (I couldn't assign a range to the listfillrange of the other combobox) i think it might be best to settle for simply copying the visible cells to a new table on the selection page (the full database is on one sheet separate from the selection comboboxes and related controls), where the user can simply see the information needed on whatever line item they want - the number of filtered entries rarely exceeds five. What i can't understand is when i query the number of rows in the immediate window from the code snip below, it always comes back as "1", whether i do so on the full range or special visible cells.

[Code] .......

To load another combobox i tired

[Code] ...........

I really would like to be able to do so for further refining, if not feasible, can work with just a display table.

Querying the reultant rows i simply tried:

[Code] ...........

and

[Code] ....

In both cases, though the filtered table had 5 records displayed, the count was... 1.

1) Can the visible cells resultant table be fed into a combobox relatively easily and if so, how?
2) With the count of rows, what am i doing wrong?

View 2 Replies View Related

Count Each Item In Filtered Range With Duplicates

Nov 20, 2006

Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...

eg

12345325
12345325
435ghfdhy
5464OKff
SEDDONF4
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
234234
66555556
66555556
66555556

As we can see here there are 14 lines of data but only 7 make up the dataset

so if X was the variable assigned to this it would = 7

Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory?

View 9 Replies View Related

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

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

Unique IDs For Records Run Each Week

Jul 19, 2013

We have our new employees fill out a form with name, address, tax info, etc. each week. (They all do it at the same time.) I then get 20 PDFs and export all the data to a CSV. We now want to import that CSV into our payroll software. However, the payroll software requires a unique ID for each employee.

So, we'll have this CSV each Monday, and each Monday, I need to "continue" the unique ID. So if this past Mondays started with 1000 through 1020, then next week, when I export, I want to start those records at 1021 and end at 1040. Then the following Monday, 1041-1060, etc.

Is there a way I can keep a record of what IDs are used and continue from that point each week?

View 3 Replies View Related

Sequencing Unique Records

Feb 18, 2008

I am working on a data integration and have transactional data sets with multiple columns of data. In reviewing the data, I can see that based on values in 2 different columns, I can identify unique transaction records. The data is already sorder by transaction # and date. Creating a pivot table gives me back summary info for header records (I can get unique header records based on a concatenation of the trx # and Date), but I am having a difficulty obtaining uniqueness for the line items. The id that I created for purposes of header record summation means nothing from a user standpoint and I need to come up with more meaningful info.

What I have and what I want is...:

Tranaction#DateWhat I want is…15801911/23/2005015801911/23/2005015801911/23/2005015801911/22/2005115801911/22/2005115801911/22/2005115801911/22/2005115801911/22/2005115801912/12/2005215801912/12/20052

If I can get the data defined like that, then I can concatenate the trx# and the increment to block out the transactions into individual transactions.

View 13 Replies View Related

Calculate The Unique Records By Day

Aug 21, 2008

I have downloaded data to an excel spreadsheet by day and need to calculate the unique records by day. Then all the daily totals should equal the monthly total if I ran the same date range for the month by removing the duplicates to get the unique records.

View 9 Replies View Related

Unique Records Search

Nov 28, 2009

I have a formula for counting unique text records. However, is it possible to count unioque records based on certain conditions. For example, I have a stats sheet for a sports league and I want to count all the unique teams for dsivision 'North'. Coloumn A has the division, coloumn B has the team they played on that day.

I want to count all the unique records in column B, but based on those teams being in the 'North Division' (column A)

I want to do this with Excel, and not in VBA.

My formula for finding unique records is:

View 9 Replies View Related

Filter Out Unique Records

Jan 8, 2008

I have a very basic table of customers. In one column there is duplicate data. I guess in most cases an Excel user would only want to filter the table to show the unique records only. In my case I want to do it the opposite way round, to delete the unique records so I have multiple occurrences of strings that appear in that one column. I've used the "Conditional Formatting" trick, which is great = COUNTIF($G$1:$G$44000,G1)>1 highlights all of the strings that appear more than once. What I'd like to do from there though is to either just have that data, and to remove the unique records.

Either that, or.. is there some way to have a field/column which shows "True" or "False" if such a string has appeared more than once in a column. Auto Merged Post;I forgot to mention.. the reason I'd want a column of "True" and False" would be because then I'd have the ability to sort/group the data into all the recurring records and all the unique ones. I'd then be able to do away with the unique ones by just copying the recurring ones.

View 3 Replies View Related

Extract Unique Records From Table?

Feb 13, 2014

I am trying to populate a tab with all the unique values from a data table. For example, list all the SKUs, SKU Names, Buying Groups, etc. for "Owl Filled Candles" on the "COLLECTION - SKU" tab. For reference, the "VENDOR - SKU" tab works perfectly. I want the COLLECTION tab to do the same thing as the VENDOR tab. The only difference is the VENDOR-SKU tab is pulling data based on a Vendor's name in a drop-down list in B3 and the COLLECTION tab is pulling data based on the Collection name in a drop-down list in B3.

I tried to just copy the tab and reset the reference cells but that isn't working.

View 4 Replies View Related

Unique Records In A Column With A Twist

Jan 11, 2013

I would like to place a formula in cells A2 down that does a running count of unique combinations of columns B and C.

For example, the first record is a white dog, so at that point there is 1 category of dog, next is a brown dog, so there there is now 2 categories etc.

I initially tried to adapt an array formula 1/COUNTIF($B$2:$B8,$B$2:$B8) but this wont quite do the trick.

View 9 Replies View Related







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