Unique Filter From A Variable?

Mar 6, 2014

I need to write code to allow the user to select a range, and let the code filter out all the unique value once the range has been selected.

The following is my code :

[Code] .....

This doesn't work, it throws the 1004 run time error and says "Method 'Range' of Object '_Global' failed.

View 3 Replies


Filter With Formula: FILTER A Range And Display The Unique Items, One Below The Other, WITHOUT Blank Cells

Feb 10, 2008

How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.

View 5 Replies View Related

Filter>Advanced Filter>Unique Failure

Jun 14, 2009

Can anyone explain why the unique filter does not produce a unique result - sample attached?

View 2 Replies View Related

Filter Out Unique Data

May 17, 2007

I have a column of data that i would like to filter using a macro and place the unique data in a separate column as completely independent data.

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

Filter Not Returning Unique Values

Apr 27, 2009

I'm using Excel 2003 and am trying to filter a list of values. The list has duplicate values in it. When I use Filter>Advanced Filter and select "Unique records only" the filter only eliminates some of the duplicates. If there are 4 values of 100, it may filter 2 of them or none at all. Ultimately I'm trying to end up with a list of values with no repeats.

View 14 Replies View Related

Return Unique Fields Only - But Not A Filter

Sep 6, 2005

>I think my issue is best displayed with an example:
>Here's my rows of data:
>Column A

I'll assume this is in Sheet1!A1:A10000.

>Is there a formula that can return the following:

View 14 Replies View Related

Automatically Filter For Unique Records Only

Mar 15, 2007

I know how to use an Advanced Filter to sort for Unique Records Only and copy them to a new column, but I am looking for a way to do this automaticly everytime I update my worksheet.

I have a worksheet that populates an e-mail distribution list based on what you imput. Some e-mails are duplicates and I would like to eliminate them automatically before I Concatenate them into a single cell.

I imagine this could be easily done using VBA, but I am not firmiliar with writing any code so it is above my head.

View 9 Replies View Related

Get Unique Records Using Advanced Filter From VBA

Mar 11, 2009

i need to use the advance filter=>unique records only feature from my macro... how would i do tat? i have 3 columns... column A has records which are repeated... column B and column C's values for a corresponding column A's value are the same...

a 3 6
b 4 7
c 8 9
d 1 2
a 3 6
b 4 7

.... and so on...

i need to use tat feature so tat i can filter column A alone and then copy column A, column B and column C's value to columns E,F and G...

View 9 Replies View Related

Unique List Without Vba Or Advanced Filter

Apr 20, 2009

I have a list of item numbers that will be repeated numerous times. I would like to extract a list of unique values that would update when a new number is added. I would really like to do this with formulas. Both of the list will be on the same sheet. I have racked my brain trying to figure out a way to do this. I thought I could use LOOKUP and MAX for the first cell, and then subtract one from the number above for the following LOOKUP formulas. That did not work. Probably because my list is not sorted.

View 9 Replies View Related

Auto Updated - Filter Unique Records

Feb 10, 2009

A worksheet has a column named "Grade". There are may entries into this column, and most are used multiple times. I'd like a list in another location (to use in a list box on a user form) that contains all of the unique entries in the "Grade" column.

I know how to do the Advanced filter for unique records, but when I add different grades to the column, the filtered list does not update to reflect the addition. Do I need to run a macro to run the filter after every new entry?

View 4 Replies View Related

Excel 2007 :: VBA Get Unique Values Set In Filter

Nov 29, 2013

[Excel 2007]. I'm trying to get the list of unique values from a Filter on a column in VBA.

I've given the column a Range Name

Dim rClient As Range
Set rClient = ws.Range("CLIENT_NAME")

Dim aClients() As Variant
aClients = rClient.???

There's nothing in the range method dropdown that obviously relates to the Filter on the range.

Is it something to do with the Table method?

View 2 Replies View Related

Count Unique Results: Function <> Filter

Dec 27, 2007

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


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.

View 9 Replies View Related

Store Unique Filter Values Into An Array

Jan 21, 2009

In an excel i have 3 columns they it contains around 12000 records

Group FA Title
A S1 bbbb
A M1 xxxx
A M2 eeeee
A S1 ffffff
A S1 pppp
A M3 aaaaa
A M2 ooooo
A M2 qqqq
A M1 ttttt

Here i need to get the unique FA, so i filter the column FA, my question is, After filter with FA column ,is there any way to store these unique FA(ie S1,M1,M2,M3) into an array using vba?

View 9 Replies View Related

Filter List Where Each Record Have More Than 1 Unique Value Assigned To It

Jun 1, 2006

I am trying to filter a list where the number of occurences of unique cost codes for each emp No is more than 1. What is needed is the emp no's who have only one costcode to have '0' in the 'no of codes' column. If more costcodes per empno, I need the first costcode to have 1, second code 2, etc in 'no of codes' column. The idea is at the end to be able to filter and delete all those with '0' in, as I only need to work with emp no's that have more than 1 'cost code'. See attached file. The records are in col A, the costcodes assigned to them in B, the formula in C, at the moment is:

=IF(AND( COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)>1),C1, IF(AND(COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)<2),(C1+1),1))

View 4 Replies View Related

Add Advance Filter Criteria For Unique Records

Aug 22, 2006

I have a folder which has 200 files. I have extracted data from these files based on autofilter criteria. But there are many duplicate records extracted for the criteria. I need only unique records . Below are the codes. Where to I add the criteria for search records:

Sub ExampleSearch()
'Note: This example use the function LastRow
Dim basebook As Workbook
Dim mybook As Workbook
Dim rng As Range
Dim rnum As Long
Dim mnum As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

View 8 Replies View Related

Get Sum From Unique Variable

Apr 4, 2013

I'm trying to find a formula to find how many times people appears in a unique project. This an example data:

Table 1
Column 1
Column 2

Project 1

Project 2

[Code] ....

I'm trying to find this result:



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

Basically is search how many times Tim appears in a unique project and summarize them.

I'm trying with sumif() in array but the results are not good.

View 1 Replies View Related

Return Unique Text Values Without Advanced Filter

May 1, 2009

I have a list of Window sizes and types in Range B4:B:43. The descriptions repeat because in Column K I am listing a location for each window in a house.

3050 SH 1/1 Dining Room
3050 SH 1/1 Kitchen
2030 Fixed Foyer
2030 Fixed Living Room

In the Same sheet Starting on B:45 I want a list of only Unique Window Types:

B:45 and Down:
3050 SH 1/1
2030 Fixed

I'd like this to automatically appear after populating the first list. I used an advanced filter the first time and it worked, but it is not reliable and sometimes returns duplicate values or give me an error message. Plus once again having it happen automatically as the list will change each time I access the sheet would be great.

View 11 Replies View Related

Remove Duplicates :: Advanced Filter, Unique Records Only

Jan 14, 2010

I'm having a problem deleting duplicates from list in excel. I’ve attached a sample. I’ve tried the following:

1-Advanced Filter, Unique Records Only

2-Remove Duplicates function in Excel 07.

3-Pivot Table

4-Colour Conditional Formatting, sorting by colour

5-B2=IF(A2=A3,”Dup”,”Not-Dup”). The entire column returns “Not-Dup”

6-I’ve tried to resolve using the fix shg & teylyn suggested to Hillto in this thread, but am unable to get the ‘Numeric’ Keypad to appear in the ‘Find’ Function.


View 14 Replies View Related

Filter And Count Unique Values In Single Formula?

Mar 18, 2014

I need to count the unique items in a range that don't contain the string "option". I would like not to use VBA for that but I can't make it work with a simple formula.

For now, I have :

[Code] ......

That doesn't count the cell with the content "option" and count correctly the other unique items. However I need to ignore any cell that contains the string "option" (with wildcards).

View 8 Replies View Related

Add Auto Filter (unique Records) Option To Macro

Dec 22, 2009

I have been modifying a workbook and the original macro will send to the sheet Results once the "Search" button is clicked, but I have found that this macro is displaying duplicate records. Can anyone help me put in an auto filter to find only unique records? My second workaround option is if someone can help me remove the go to/select sheet option from the "Search" macro so that a user will not automatically be sent to the Results tab and will instead hit the "Confirm Category Selection" button (which auto filters before sending the user to the Results tab).

View 12 Replies View Related

Advanced Filter :: Copy To New Location, Unique Records Only

Jan 8, 2010

If you navigate on the file menu in the excel window to: Data>Filter>Advanced Filter

Then select:

Copy to New location, Unique records only. You can arrive at the macro

View 2 Replies View Related

Filter Show The Most Recent (highest Row) Unique Entry

Jul 30, 2008

Is there a way to make Advance Filter show the most recent (highest row)unique entry instead of the first entry (lowest row)?

For instance if a spread sheet has

1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

I want to keep the "1 3", "2 3", and "3 3" entry, not "1 1", "2 1", and "3 1".

View 9 Replies View Related

Advance Filter Check Unique Column Entries

Jul 7, 2009

How do you remove entries from a column such that only unique entires are left?

Say the spreadsheet contains only a single column:


I tried an "advanced filter" with "unique records only" checked. It gets rid of one david, but not 2 (there are originally 3 of them, and we want to leave 1)

This is the result.

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

Pasting Variable In Filter In VBA Macros?

Sep 30, 2013

I am trying to write a macro where I have to select a name, copy it and search in a Pivot filter if a certain condition is met. I am stuck at a place where I am unable to paste the selected value as a variable.


For Each cell In .Range("I2:I" & .Range("I" & .Rows.Count).End(xlUp).Row)
If cell.Value = True Then
Range("G" & N).Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("QC POC").PivotFilters.Add _
Type:=xlCaptionContains, Value1:="selection.paste"

The portion which is in bold (Value1=) needs to contain my selection (which is actually a name)

View 8 Replies View Related

Filter By Variable Captured From Input Box

Jul 17, 2007

I have a Workbook containing a list of records. I'd like to create some code to

1) Get the user to input their initials when the workbook opens, and capture the response
2) Clear any filters currently applied
3) Apply filters to a non-continous range of columns
4) Apply the input response as the criteria to one of the columns, and 'blanks' to another 2 columns

Private Sub Workbook_Open()
Static varUser As String
'sets varUser variable as String & stores it
varUser = InputBox(Prompt:="Enter Intials (leave blank to view all)", Title:="Login")
'Activates input box to capture value
Dim ContractsSheet As Range
Set ContractsSheet = Worksheets("Contracts").Range("A1:AA999")
ContractsSheet.EntireRow.Hidden = False....................

View 4 Replies View Related

Deleting Duplicate Row (8 Cells) And Shifting Unique Rows Up - 3 Columns As Filter

Feb 4, 2014

I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).

As filter I would like to be used A,B,C columns.

Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1

Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1

View 1 Replies View Related

Counting Unique Values Based On A Variable

Dec 15, 2009

I'm having difficulty trying to write a formula for the following type of data:

[data] ....

This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...

View 9 Replies View Related

Automatically Filter Pivot Table Using Variable?

Dec 3, 2013

I have a workbook with several worksheets and several pivot tables. Based on the selection from a drop down box on the first tab, I want to automatically filter pivot tables on subsequent tabs. Is that possible?

View 2 Replies View Related

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