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.
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...
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
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....
Sub filtertest() Range("C17:L33").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ Range("C13:C14"), CopyToRange:=Sheets("Combined Summary").Range("B7") End Sub
This works fine - it filters the data in C17:L33 on the source sheet and then copies it to the combined summary (beginning in cell B7) but when it copies the data it also copies the header row - i only want to the data to be copied? - how can i amend the above??
Also - there will be a few tables i'll be amending the above code to work on as i'll be pulling them into 1 summary - how can i amend the B7 section so that they paste into the next free row - because at present they will just past over one another?.........
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.
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.
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.
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.
I'm working with many rows of data (500,000+) and many columns. To simplify my question, I'm going to provide a simple example using made up numbers and only the columns I'm concerned with.
I am trying to remove the docs that have two Bill types that cancel each other out, where the qtys match and highlight the rows where the qtys don't match. The macro needs to have the positive and negative bill types programatically entered, where for example F1 and F2 are positive and S1 and RE are negative. Keep in mind the data may not necessarily be in order as it is above.
So for example with data above, the rows for doc 777 would be removed completely because the item numbers are the same, the qty is the same, and the bill types oppose each other. Doc 123 and Item 10 lines should be highlighted since their bill types are opposed bu their qtys don't match.
I hope this makes sense. I tired to achieve this using multiple loops and arrays, but ran out of memory when working with the entire set of data. I'm assuming their must be a better way to do this, I'm hoping some of the intelligent individuals here will be able to point in the right direction.
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?
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
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).
I have been trying for a day now to figure out how to remove the unique cells and keep the duplicates only. I have tried a macro but it does not seem to work. Has any one run into this before. I have attached a sample what I would like to do is remove all non-duplicates from column A. The only macro I could find was this:
I'm having an issue where advnace filter isnt working and I need to get all of the unique entries.
I don't know if this has anything to do with it but I got some erros saying the numbers were being pu as text so I changed the cell tpe to a custom with 00000000 because the unique number I need to filter is 10 digits long.
As another alternative I would paypal some one a few bux if they can help me just compare two lists and make a report with the number of netries that show up in one list and not the other and vice versa.
In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.
Example: CT11:CT14 looks like 5x20 6x4 5x20 5x8 and so on and so forth..
I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.
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...
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?
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?
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.
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?
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
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.
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.
