Macro For Deleting Duplicates / Comparing Rows And Retaining Minimum Value?
Apr 6, 2014
I have data from Columns A to D. I want to do the
following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in column B are also duplicate.
3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.
For example:
A B C D
Row1: Abby 04/01/2014 7:00PM 0.98437
Row2: Sam 04/01/2014 9:00PM 0.35627
Row3: Abby 04/04/2014 7:00PM 0.68932[code]....
View 13 Replies
ADVERTISEMENT
Mar 17, 2007
I have two seperate lists. One on Sheet 1 and One on sheet 2. What I need to do is take the list on sheet 2 and compare it to the list on sheet 1. If any of the items appear on sheet one, I need to delete those items form sheet 2.
So in the end the only items left on sheet 2 will be items that dont match any items on sheet 1
View 9 Replies
View Related
Mar 22, 2013
I have the following problem within Excel. I have a dataset which contains duplicate values within a column(lets say A). I need to delete these duplicates in column A for a range of rows, where the range of the rows is based on column B. So for a given value in column B, lets say 5 which is 6 rows long all beneath each other, I need to delete the duplicates in column A.
A B
1 2234
4 2234
5 2234
4 2234
2 2234
3 2234
5 2234
5 2657
8 2657
9 2657
10 2657
8 2657
9 2657
2 2657
So above the red numbers need to be removed, automatically, since we have about 8000 rows in our dataset.
View 7 Replies
View Related
Jan 25, 2014
I'm trying to remove duplicates without deleting the rows they were in (instead just being left with blank spaces).
For example Column A might look like this:
Column A
1
1
1
1
2
2
2
3
I need it to look like this:
Column A
1
2
3
How I can delete these duplicates without doing it one by one?
View 1 Replies
View Related
Jun 9, 2009
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
View 9 Replies
View Related
Jan 9, 2008
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"
My code if needed is:
Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
View 9 Replies
View Related
Jul 16, 2013
I have a spreadsheet for recording property maintenance. All properties are grouped by a city zone in a specific colour, and there is conditional formatting on this. Also drop down lists for properties, contractors etc.
This all worked well, until I needed to start inserting rows. All the conditional formatting and lists didn't replicate on the newly inserted rows.
Also, we need to assign a unique reference number to each row but currently manually inputting them - very inefficient way of doing this as they will inevitably get mixed up and confused as rows are removed.
Spreadsheet.jpg
View 9 Replies
View Related
Mar 19, 2009
I want to delete every second row, because I have data with 0.25m interval and I want 0.5m interval. Why I try the following it doesn't work?
View 2 Replies
View Related
Jul 25, 2012
I'm looking for a macro that will delete a row with a certain value (0.00) and the row below it.
View 9 Replies
View Related
Jul 28, 2007
I am looking to find and delete email addresses in this case *@aol.com. I have only one column which is full of email addresses, I just want to delete all of the @aol.com ones in this case. This is the macro I am trying to use, but it does not work.
For Each cell In Range(Range("a2"), Range("a65536").End(xlUp))
If cell = "*@aol" Then
Range(cell, Cells(1, Rows.Count)).EntireRow.Delete
Exit For
End If
Next cell
View 3 Replies
View Related
Oct 15, 2008
I'm trying to compare two lists of songs to see which songs are not shared by both lists. I wrote a simple function to try to do this but ran into problems pretty fast. I want to create a function which will tell me if the song in a cell (lets say cell B2) is the same as any of the cells in Column A.
View 2 Replies
View Related
Aug 24, 2009
I have two columns: Column A with SS#, Column B with loan #. i need a formula that would return 2 different loan #s for same SS# on a different worksheet. Example
SS#:Loan #ResultSS#Loan #123567812312356781231235678345123567834534578917862950013351
Spend all morning on this. Tried various INDEX, COUNTIFs, MATCH, VLOOKUPs but when looking at the same SS# in a column it always returns the first Loan#.
View 13 Replies
View Related
Jan 20, 2014
I want to write a macro that would delete the rows if the sum equal to zero and the description is identical. For example:
Column a: Column b:
100 Apple
200. Apple
300. Grape
-100. Apple
-200. Apple
400. Cherry
-400. Cherry
The answer should be 300 grape.
View 2 Replies
View Related
Apr 18, 2014
I am looking for code to put in a macro that will go through a tab of data and delete the rows that have merged cells in them. The number of rows that there will be will never be a constant because the people using it will be pasted in information from another source, deleting the lines that have merged cells then run a macro with stuff that needs to be done to the info, but I would like to save them the step of delete the rows that have cells.I think it is Office 2013 I am using. I am not at that machine right now so I can't check.
View 4 Replies
View Related
Mar 3, 2008
I am able create a macro using the find function to find the next blank row, but I would like to have it select a range of rows down that I can then delete. Each time I run the macro the next blank row may be different thant the last, so it can't be a set number of row numbers each time. I could also possibly use the print area function if it would be easier.
Here is what I have so far, what is in red is where I need it to vary from next active blank row down to R2001C14, and then delete all the active blank rows ...
View 9 Replies
View Related
Jan 1, 2010
I am trying to solve is as follows, I have groups of 20 numbers in columns, one per column and would like to compare the groups to determine if any group is duplicated on another line.
I had been looking at using VBA to copy the numbers to an array, sort from lowest to highest, concatenate and use that to compare each group. If possible however if there is a suitable formula it would be preferable.
I have attached a small sample to illustrate.
View 8 Replies
View Related
Apr 1, 2014
I have a excel file with more than 10 sheets and every sheet has a title on its first three rows.
I want to delete first three rows in all sheets except parent sheet .
View 4 Replies
View Related
Oct 29, 2009
Is there a way for a macro to be not active when trying to insert a row or a way to have the macro understand that it's just a row shift? I'm trying to have a time stamp that anyone changes the value in a column. The following code generates an error 1004: application or object defined error when I insert or delete a row.
View 14 Replies
View Related
Sep 18, 2013
My excel contains first 5 rows of heading information and the rest of the rows contain the data. I want to select a specific row from the data and run a macro that would delete all other data containing rows.
This is what I have now:
Code:
Sub DelRows()
Rows(6 & ":" & ActiveCell.Row - 1).Delete
Rows(7 & ":" & 65000).Delete
End Sub
Everything is fine if I select any data row except the first one (R6). In that case, the macro doesn't work as it should, deleting a row from the heading ones.
View 1 Replies
View Related
Jul 7, 2008
Create a smart macro which will remove all references to the #REF! which is left when rows are deleted. It would have to remove all trace of it from any equation it may be in (i.e. if it was in an averaging equation, it would need to remove the preceeding comma as well:
=average(a1,a2,!#REF!,a4 .........)
View 9 Replies
View Related
May 7, 2009
I m writing a formula that will highlight duplicates. I want to use the supplier code (column D) as the search criteria. can the formula identify these duplicates by entering the word 'duplicate' in column L.
View 3 Replies
View Related
Sep 7, 2008
I have a large spreadsheet of over 5,000 rows and 20 columns. What I would like to be able to do is find a formula(?) that will enable me to retain rows of data when Cells in column B and column E contain duplicate data but delete entire rows if the contents of cells in column B and E are not duplicated.
For example:
Column B contains Part Numbers
Column E contains Manufacturer
If a part number (Cell B)has been made by two or more manufacturers (Cell E), retain all relevant rows.
If a part number has only been made by one manufacturer, however many times, delete all relevant rows.
What I'm trying to be able to achieve is retain data so that I can analyse it when 2 or more manufacturers have produced the same parts.
View 9 Replies
View Related
Jun 6, 2013
I'm working through a filter macro to delete unecessary rows of data from my dataset.
- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"
The macro filters the range fine, but when if comes to deleting the row, the macro stops.
Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then
With Range("CanadaData")
[Code] ......
View 2 Replies
View Related
Aug 23, 2007
I realize that there are 100 posts asking this same question but I believe this question is unique. My actual data if far more complex, but I have attached a simple workbook to illustrate my problem. It is my understanding that the following code will generate a unique list of values. However, it does not work if an AdvancedFilter has been used previously in the code.
Sheets("Sheet1"). Range("B1:B6").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("E1"), _
Unique:=True
I have been able to solve this problem by adding "CriteriaRange:=vbNullString" to the above code.
Sheets("Sheet1").Range("B1:B6").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=vbNullString, _
CopyToRange:=Sheets("Sheet3").Range("E1"), _
Unique:=True
Here is my question: Do you need to reset the value of "CriteriaRange" each time you use AdvancedFilter, and if so, is setting CriteriaRange to vbNullString the best way to do so.
View 7 Replies
View Related
Mar 18, 2014
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
View 1 Replies
View Related
Mar 3, 2008
I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
View 9 Replies
View Related
Jan 27, 2014
sample3.xlsx
I'm still having issues with this workbook. I cannot use a pivot table to fix it, I don't understand them and it confuses me greatly..... so I'm at the mercy of either a formula or macro. I need to combine the duplicate part numbers (a), total the quantities, average the price (d), and total the amount of the part (e). I'm having a very difficult time with it.
(I have a sample attached that is file sample 2, and the entire spreadsheet attached- sample 3)
View 4 Replies
View Related
May 13, 2014
I'm trying to take column A (number of records can change from time-to-time and may contain blanks), copy that to column B less duplicates and then use a count forumla to count items in column B based on original list in A. I am aware of how to do this in Excel but am interested in VBA.
Example
Column A
Jim
Jim
Mike
Jeff
Jeff
Jeff
[Code]...
View 4 Replies
View Related
Feb 24, 2010
Does anyone have a utility which can be used to select a specific column i.e. B:B, which then 'selects' all duplicate values in that column prior to deleting them ?
View 4 Replies
View Related
Aug 28, 2009
way to join two or more tables in excel adding new rows and columns.
In this way:
Input TABLE1
ID, Tag1, Tag2
1, alfa, big
2, delta, small
Input TABLE2
ID, Tag1, Tag3
2, delta, green
3, gamma, yellow
Output RESULTING TABLE
ID, Tag1, Tag2, Tag3
1, alfa, big, -
2, delta, small, green
3, gamma, -, yellow
View 9 Replies
View Related
