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 B C
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
ADVERTISEMENT
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.
[url]
View 14 Replies
View Related
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
Oct 25, 2006
At the moment i am having great trouble taking two sets of records, comparing the two based on certain criteria and then extracting any records (rows) that do not appear twice, that is unique records.
So here it is:
I have two lists of sales.
1. our list from our point of sale system with order numbers and other details, entered by the sales agent.
2. the company that provides the products we sell for them sends us a list of these sales back to us from their end so we can see outcomes of these sales, that is the progress of the order, like cancelled, accepted, etc
The two lists need to be compared to see what sales are missing from either one..
so, I could colour the second list RED and the first list GREEN and add the second list underneath the first list (on the same sheet) and then sort by ORDER NUMBER, which would provide a red, green, red, green, red, etc pattern and i could easily identify sales that are unique, but there are so many thousands of sales this manual process is impossible.
IS THERE A WAY TO AUTOMATICALLY COMPARE THESE TWO LISTS BASED ON ORDER NUMBER AND SHOW ONLY THE RECORDS THAT ARE UNIQUE? THAT IS, GET RID OF SALES THAT APPEAR TWICE AND LEAVE ONLY RECORDS THAT I HAVE TO LOOK INTO WHY THEY ARENT ON BOTH SALES SHEETS...
Here is an example of the data i am using:
ORDER #
1630923-
1634849..
1634849..
1634972+
the numbers do NOT have the .. - or + next to them, thats just there to show you what list each is from.
so as you can see the order numbers with ".." next to them are reconciled, in that they have a partener record and do not need to be shown at all..
and the order numbers with "-" next to them are from our list
and the order numbers with "+" next to them are from THEIR list
i want to end up with a list like this:
ORDER #
1630923
1634972
1635643
1641970
1648112
1649716
1653854
View 10 Replies
View Related
Nov 2, 2008
I am trying to use an advanced filter to extract records that meet the criteria in the blue input cells. I can't get the criteria correct to allow me to meet the 3 conditions. There are duplicate names in the list so I will need to use unique records only option....
View 9 Replies
View Related
Jun 14, 2009
Can anyone explain why the unique filter does not produce a unique result - sample attached?
View 2 Replies
View Related
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
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.
Example:
B K
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
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
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
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
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
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
May 1, 2014
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
View 4 Replies
View Related
Jan 14, 2010
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
View 9 Replies
View Related
Mar 15, 2007
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
View 5 Replies
View Related
Apr 8, 2014
I have a large Excel with details of 1,000 staff
Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc
I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g
Frank, James
Wilkonson, Paul,
etc
In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR
Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?
View 11 Replies
View Related
Aug 5, 2014
I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
View 8 Replies
View Related
Jul 25, 2006
I have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.
View 9 Replies
View Related
Sep 8, 2009
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
View 9 Replies
View Related
Dec 17, 2012
why the Advanced Filter works in the macro but not in the UDF?
Code:
Public Function strUniqueVal(rng As Range) 'rng variable must include sheet name
Dim c As Range
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
[Code]....
View 2 Replies
View Related
Mar 24, 2007
Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).
Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False
Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?
View 9 Replies
View Related
Oct 28, 2008
I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.
I have another worksheet that contains a list of parts in one column.This list is varying in row length.
I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.
When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.
View 9 Replies
View Related
Aug 23, 2009
I have a huge list (over 900,000 rows) of text codes (e.g. ABC-12345) on Column A Spreadsheet 1 and would like to remove the ones that are not included in Column A Spreadsheet 2.
I changed all of the text on Column A Spreadsheet 2 to have the in front of it (e.g. ABC-1234) and tried to run the advanced filter but it does nothing.
View 9 Replies
View Related
Feb 9, 2010
This code works, but I would like to cut (remove) the records from the original record list.
Range("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("H1:H3"), CopyToRange:=Range("J1:O1"), Unique:=True
View 9 Replies
View Related
Jan 17, 2009
I need to lookup and concatenate all the header values where the value in the corresponding row equals a certain value. For example, column D, has the column header value if the cell values equals "X". Likewise column E has the column header value if the cell values equals "Y". I have too many columns (this is just a simplified version) to use nested if statements.
resource1resource2resource3 XResources YResources
Jeff X resource2
John X X resource2 resource3
Jim Y resource1
I'm not an excel newbie, but I keep thinking there must be a simple solution that I am missing.
View 6 Replies
View Related
Feb 15, 2010
i am trying to work out the coinage for wages ie. 5546.75 needs to brake down into dominations ,notes and change i live in south africa so we use the desimal system. i have tried to use floor and mod as formulas but at some points it returns an error or incorect result.
View 8 Replies
View Related
Jun 21, 2006
I am trying to create a function using vba and the advanced filter function.
The spreadsheet I am using changes from week to week so it can have different amounts of columns and also rows.
This is my code.
Sub FilterCGML()
Dim rngToFilter As Range
Dim rngFilterCriteria As Range
Dim LastRow As Long, LastCol As Integer
With Sheets("Main FTE")
Range("b2000").Value = "Cost Code"
Range("b2001").Value = "5*"
It works ok if I have values in columns A:X but if for example I only have information in A:S I get a runtime error.
I know how to find the last column and last row by using
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column
But I don't know how to modify my code to use these.
View 4 Replies
View Related
Jul 13, 2006
I am trying to perform a function that searches through the first column for any active "A" account and then copies the corresponding rows information into another worksheet. I think I need to use the advanced filter function however I can't seem to figure it out.
View 9 Replies
View Related
Dec 4, 2006
I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.
View 9 Replies
View Related
