Free Microsoft Excel 2013 Quick Reference

Use Advanced Filter in VBA to create unique list

I've built a MASSIVE Excel macro for one of our ICP-MS instruments. The very last part of the app is a reporting function, and the last piece of THAT is to search a column that has LIMS record numbers in it and create a comma-separated list of the distinct LIMS numbers. The problem is that when I run the following line of code against the following values, it returns the following values.

Cell Value B13 205463 B14 205463 B15 205463 B16 205463 B17 205463 B18 205463 B19 206121 B20 206121 B21 206121 B22 206121 B23 206121
returns

Cell Value AA6 205463 AA7 205463 AA8 206121
when I run the following code


	VB:
	
 TryAgain() 
    Range("B13:B23").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA6"), Unique:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am at a LOSS! No matter what I do, the code returns two instances of whatever the first value is that it finds more than one instance of. If I switch the 206121 values with the 205463 values, and put the 206121 values at the top, the code returns 206121, 206121 and 205462. Can ANYONE tell me what I'm doing wrong? I've cleared the cells and manually entered values to make sure there is not a stray space or other character that IS making two values distinct that appear to be identical...completely baffled on this one. BTW, if I replace the first value (cell B13) with a null, the function returns nothing. If instead I replace the SECOND value with qa null, I get
205463

205463
206121

(The second cell down is null)

ANY suggestions?


Post your answer or comment

comments powered by Disqus
I was trying to Generate unique values from a set of values.

I have written this code for that.


	VB:
	
 MakeUniqueList() 
    Dim lasth As String 
    lasth = "h" & lastrow() 
    Dim r As Range, r1 As Range 
    Set r = Range("H4", lasth) 
    r.AdvancedFilter _ 
    Action:=xlFilterCopy, _ 
    CopyToRange:=Sheet3.Range("H4:H1000"), _ 
    UNIQUE:=True 
     
     'Range("H4").Delete Shift:=xlShiftUp
    Set r1 = Range("H4", Range("H4").End(xlDown)) 
    r1.Sort Key1:=r1, Order1:=xlAscending, Header:=xlNo 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem i am facing is that it sorts the values from the list but i dont know why its printing the first value twice.

Please let me know if that can be done by some way.

Thanks in advance

Hi everyone,
i am working on a macro which takes values from sheet 1 and paste unique values in sheet2.

I have written code for this but the problem i am facing is that it generates first value twice.


	VB:
	
 Range 
Set r = Range("H8", "H240") 
 
Set r1 = Range("H8", Range("H8").End(xlDown)) 
r1.Sort Key1:=r1, Order1:=xlAscending, Header:=xlNo 
 
r.AdvancedFilter _ 
Action:=xlFilterCopy, _ 
CopyToRange:=Sheets(3).Range("H8:H100"), _ 
unique:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This macro works fine but the value at H8 gets repeated twice.

Please help m stuck at this point.

THanks in advance.

I checked the Help area and got lots of good information about using advanced
filters in Excel. The question I still have is: How do you filter on a text
field to find words ending in a letter or combination of letters? For
example, I want a list of the records where a particular text field ends in
the letters "en".

I tried using "*en", but that also gives you words where the "en" appears in
the middle of the word.

I checked the Help area and got lots of good information about using advanced
filters in Excel. The question I still have is: How do you filter on a text
field to find words ending in a letter or combination of letters? For
example, I want a list of the records where a particular text field ends in
the letters "en".

I tried using "*en", but that also gives you words where the "en" appears in
the middle of the word.

Hi, I am a complete newb to excel, and haven't come across the solution for this yet.

I am intending on using excel to create unique phrases. The first column will be the phrases I will be using, and the second column will be the middle part of the unique phrase I want my spreadsheet to generate.

example.jpg

As you can see, all the phrases in the example are 100% unique. 'Eggs and Oatmeal' will not be created because 'Oatmeal and Eggs' already exists. How do I do this?

Any help would be greatly appreciated!

I've been putting together an excel spreadsheet that uses dynamically-named lists. It works pretty well, but the magnitude of the spreadsheet is getting to a point where doing this through the cells is becoming a bit problematic. I'm looking for ways that I can use VBA to create the list for me, but I'm having some trouble finding relevant commands.

Consider two worksheets: RawData and ReferenceData

RawData consists of a series of delimeted text, and I am interested in the contents of column B. I cannot guarantee that the series wil be sorted. The data of interest starts in Row 5, and extends for an uncertain number of rows (I could paste different data there with more or fewer rows). However, the last row of applicable data will be blank.

So, out of the whole sheet of data, the "important" parts might look like:
[RawData]B5 => "test1"
[RawData]B6 => "test3"
[RawData]B7 => "test2"
[RawData]B8 => "test4"
[RawData]B9 => blank

ReferenceData consists of a several columns of data against which we will be checking the contents of RawData. For convenience, consider that the column I want to check the RawData against (this time) is column G. Each reference column could vary in length though (column A might only have 5 entries, but column G could have 12). This data is sorted. Again, the last cell in a column of the applicable reference data will be blank.
[ReferenceData]G1 => "test1"
[ReferenceData]G2 => "test2"
[ReferenceData]G3 => blank

I would like the macro to kick in automatically whenever something on RawData changes (i.e., I've pasted new data). When activated, the macro should go down RawData cell by cell, and see if the value lies within a particular ReferenceData column. If so, the RawData cell value gets added to List. If not, the RawData cell value skips to the next cell.

So, after the macro runs, I should have a list, that I can refer to elsewhere (say, in data validation, or used with other lists merged into a single new one). List, then, would be "test1, test2"

And so I present a teaching opportunity I know how to describe what I want done, but need some guidance on the syntax.

Thanks!

Hi,

I am trying to migrate a model that I built in Excel 2002 to Excel 2007. The model has an advanced filter in it to get unique values. However in 2002 the filter worked correctly but in 2007 it produces nothing. The code excerpt is below :-

Worksheets("Transaction Data").Range("PCT_List").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Transaction Data").Range("criteria"), _
CopyToRange:=Worksheets("Transaction Data").Range("Unique_PCT_List"), Unique:=True

Any ideas ? Thanks

Kaps

I wonder if this were possible:
Use MS Query to construct a SQL statement and use the statement in VBA code
(ADO, ODBC connecting to Interbase), but don't let it run the statement.
So just use the MS Query as a SQL wizard, incorporated in VBA code.
Doing this would save a lot of coding to get a similar wizard, although it
shouldn't be too difficult either.
One drawback I can see is that MS Query won't let you do a WHERE condition
on a field that is not in the SELECT
clause. The good thing though is that it will automatically add any joins,
although I am not sure how it will handle
joins that are not inner joins.
Hope this explains it clear enough and thanks for any advice.

RBS

I am try to figure out a way to use Auto-filter when some of the cells in a
worksheets are protected. I don't want users to be able to change the data in
the sheet but they need to be able to use Auto-filter in order to do some of
their work. The problem is, anytime we protect the sheet, they can't use
Auto-filter.
Is there a work around for this?

Thank you.

I'm trying to find the right wording to get my explanation out. Please bear with me (google didn't).

I need to be able to pull information from 5 columns on 5 different pages, and pull all of the unique names out of it. From there, I need to create a list of all of these names. More often than not, this could only be three names, but I still would like this to be automatic because eventually it could be 50 or 60, and manually editing this list would get tedious. Lastly, is it possible to have a list create cells? For example: I have information on row 4, this one will start on row 6. My next group of information starts on row 14. That leaves 7 rows for information. I don't want to leave a large gap at the top of the page, so I'd like to be able to create cells if the list I'm trying to create has more information on it. Seems plausible in my mind, but I'm still new to excel.

HOW TO: Use Advanced Filter to Exclude Records in Excel 2000

This step-by-step article explains how to use the ISNA and MATCH worksheet functions to exclude records in a Microsoft Excel database that do not match another list. How to Create ...

Hello, I hope some of you can help me because I'm pulling my hair out at the moment. I have a large dataset that will be presented in a pivot table which will be very adjustable through selections of cascading combo boxes. I'm comfortable with manipulating pivot tables with VBA and the combobox results but I'm not getting anywhere with creating unique lists that will cascade with combobox choices.

My data is in range A:D, this would normally have numerical data attached but I've stripped it out hence why the data may look slightly odd. I've used advanced filters through VBA which is very useful to summarise the data using parameters from the combobox (represented in range F:I) but it's keeping the data relational (range K:N) so I'm ending up with duplicates. My bright idea was to then use another advanced filter individual columns in range K:N and I should have unique values in P:S, but it's not turning out like that! Range U:X is what I'd ideally have in range P:S... The filter button triggers my basic code.

I know this should be simpler than I'm doing it but can anyone offer some help or suggestions to how I should do this? I've looked at data validation, advanced filters, vba loops but I'm still at square one. Any help would greatly be appreciated! Thanks

How to Use Advanced Filter to Exclude Records

You can use the ISNA and MATCH worksheet functions exclude records in an Excel database that do not match another list. To create exclusion criteria, use the following formula ...

I have searched for three nights through Ozgrid and other forums, as well as all my Excel books, to try and find the answer to my problem. In theory, it should be ridiculously easy, but I cannot get AdvancedFilter to work in VBA! I have copied and pasted ad nauseum, but there is clearly a trick that continues to escape me.

Here is the issue: I have a worksheet, named "Invoices", with column headings: InvNum, Date, Customer ID, Qty, ProdCode, Price, Disc, Total. What I want to do is filter the entries between two dates which the user can select via a userform. These two dates are pasted to a worksheet, and I have created a named range "Criteria" relating to these two adjacent cells, containing a start and end date. I have set up a dynamic range of all entries in the "Invoices" sheet, called DynamicInvoiceRange (using Offset). Once filtered, I want to copy the results to a sheet named "StoreResult."

I am sure this sounds like VBA 101 to you, but I have now given up: I get the filter to copy the records to the StoreResult sheet, but no filtering takes place, and despite all my efforts, all records get copied every time with no filtering on the invoice dates.

What am I missing here? Any help will be most sincerely appreciated.

hi all,

i am wondering if using VBA to create standard add ins, the add ins after installed will show at menu bar (on the right of help menu). FYI, i'm still using Excel2003. If can appear there, how? If cannot, normally, how to execute the macro stored inside a .xla? (I am referring to the book VBA and Macros for Microsoft Excel by Bill Jelen and Tracy, chapter 25)

Thanks....

I need to create 50-60 bingo cards for my sister's bridal shower. I wanted to create these using excel. I need to create unique cards so that not everyone bingos at the same time (I only have so many door prizes to give away!)

The basic setup is this: under "B" there are 5 spaces, and each space must have a unique number between 1 and 15 (no repeats). Under "I" there are 5 spaces, and must use a unique number between 16 and 30, "N" has 5 spaces using a unique number between 31 and 45, "G" has 5 spaces using a unique number between 46 and 60, and "O" has 5 spaces using a unique number between 61 and 75. A sample card is below:

B I N G O
14 16 37 58 62
2 17 45 57 73
8 30 36 60 66
11 22 39 47 61
12 23 43 56 68

I have tried to use the "randbetween" function, but this creates repeats and I would have to go in and manually change the numbers that repeat.

Any ideas? I would prefer not to use macros or VBA, as I am not very good using those.

Thanks!

Hello, I cannot seem to get a simple advanced filter to work using VBA. It works fine when I perform the task manually on the same data range and criteria, but when I use the code below I get the message: " Run time error '1004' The extract range has a missing or illegal field name". My code is below:
Sheet12.Activate
Sheet12.Range("A1:R20000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("t1:t4"), CopyToRange:=Range("AA1:AR1"), Unique:=False
ActiveSheet.Range("AA1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Any help would be much appreciated.

Thx, Wratpack

I am a novoice in VBA, trying to run a macro using Advanced filter.

If someone can help me find the reason for error and means to overcoem it.

Dim wsData as Worksheet
Dim wsList As Worksheet
Set wsData = Worksheets("Data")
Set wsList = Worksheets("List")

If Target.Address = "$C$9" Then

wsData.Columns("A:Z").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=wsList.Range("J1:J2"), _
CopyToRange:=wsList.Range("A1"), Unique:=False
' ( system gives error on advanced filter condition)
End If
End Sub

thanks

6/6/2005 12:30 DBAB Media VIA.B
6/6/2005 14:00 DBAB Media SFA YBTVA
6/6/2005 14:30 DBAB Media CMCSA CMCSK HHS
6/6/2005 15:00 DBAB Media ADVO PIXR SGA
6/6/2005 15:30 DBAB Media VCI WON

I am trying to use advanced filter to search for a specific group of
stocks (eg. I1:I100) in columns D-F and return only the stocks on my
list. eg. PIXR is on my list so the filter would return:

6/6/2005 15:00 DBAB Media PIXR

I am having problems getting the criterion correct. Can this be done
with advanced filter or is there an easier way?

--
Potatosalad2
------------------------------------------------------------------------
Potatosalad2's Profile: http://www.excelforum.com/member.php...o&userid=20834
View this thread: http://www.excelforum.com/showthread...hreadid=377137

6/6/2005 12:30 DBAB Media VIA.B
6/6/2005 14:00 DBAB Media SFA YBTVA
6/6/2005 14:30 DBAB Media CMCSA CMCSK HHS
6/6/2005 15:00 DBAB Media ADVO PIXR SGA
6/6/2005 15:30 DBAB Media VCI WON

I am trying to use advanced filter to search for a specific group of stocks (eg. I1:I100) in columns D-F and return only the stocks on my list. eg. PIXR is on my list so the filter would return:

6/6/2005 15:00 DBAB Media PIXR

I am having problems getting the criterion correct. Can this be done with advanced filter or is there an easier way?

Hi

I am just asking whether it is at all possible using Advanced Filter with NOT and unique records? To explain my logic, this is what I am trying to achieve, I have a column called option codes, in this column there are duplicate values and values that have the word "NULL". Now, what I want to do is to filter on this column all unique values but I also want to keep all rows that have the word NULL in that column, too. I want to do this in one filter rather than having to go back and forth.

Is there away to do this?

Example output would be:

AA
BB
CC
NULL
NULL
.
.
.
.
.

Dear All,

I need your help in creating sheet using advance filter. Excel details are as follows

Input data

There are various entries in various columns
Details indicates a particulars area
There are various areas in that entry

Output
Output should be in such way that if we enter name of particular area, corresponding details should automatically appear.

Please suggest how to create such sheet in arrange manner.

Can you use or apply advanced filters in a shared workbook?

I have a workbook project where I am dynamically applying advanced filters through VBA.
The intention is to share the workbook at some point for data entry among a group of people. I did not see advanced filtering listed in Excel Help as one of the features that are not available, but I did note that if I try to apply one through the menu bar it is greyed out in a shared workbook (autofilter is still available).

I recently posted that I am having a problem with a custom toolbar that I have attached to my workbook; the problem being that when the macros are disabled I cannot delete this toolbar after the workbook is closed, thus still being available to the user in a non-functional form, which I don't want. The code uses the BeforeClose event to delete the toolbar to accomplish this, but obviously not when the user disables the Macros. Is there a way to use VBA to create the toolbar in the first place? I was discourage because the help file indicated:

"All host applications have an extensive interface for adding and designing custom toolbars (adding built-in buttons, adding macros as buttons, even adding pop-up controls to toolbars). The design-time changes you'll usually make from Visual Basic code are ones that add or modify combo box controls. Otherwise, working with toolbars in code is almost completely limited to making run-time changes (changing the button state, changing the button appearance, changing the button action, and so on)."

Note: I am not looking for a way to bypass the security feature, just to make the toolbar unavailable when macros are disabled by using code to create the toolbar, or by some other, creative, means. If anyone has any ideas for me it would be greatly appreciated.

Thanks,


No luck finding an answer? You could always try Google.