Deleting Rows With Duplicate Values In A Column

Jun 26, 2009

In Column "C" there is a list of numbers ( these number realte to incidentsm and are called incident IDs) and I want to delete the rows that have duplicate incident IDs.

the row of numbers looks like this ....

View 9 Replies


ADVERTISEMENT

Deleting Duplicate Rows And Summing Values?

Aug 6, 2012

I have a sheet whereby in column A i have company names and column B how much is invested in this company so:

A B
AAPL 10
GOOG 5
MSFT 15
APPL 5
IBM 20
GOOG 10

What i would like to do via macro is take all the duplicates sum them so that i see AAPL as 15 but only be left with one row. So we would be left with only 4 rows each with their summed weight.

View 9 Replies View Related

Deleting Duplicate Rows Based On Cell Values

Nov 28, 2013

I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag

Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.

And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.

View 1 Replies View Related

Deleting Duplicate Rows Keeping Lowest Value In Column B

Jul 30, 2009

I need the macro for deleting duplicate rows only it should keep one lowest value row.

for e.g.

Column A Column B

TS1234 100
TS1234 50
TS1234 200

Macro will compair dublicates in column A & it will keep lowest value row (i.e. 50 value in Column B), other dublicate rows will be deleted.

View 12 Replies View Related

Deleting Rows Based On Values In Certain Column

Oct 24, 2006

How can I delete rows in a worksheet using values in a certain column as criteria?
Example: I want to delete all rows that show a value of zero in column k. How is this done?

View 6 Replies View Related

Deleting Duplicate Rows

Oct 14, 2009

In my Excel 2003 worksheet I need a macro to search column B for duplicate entries. The data in column B is both numeric and string. If there are any duplicate entries, then I need the duplicate rows to be deleted.

View 4 Replies View Related

Delete Duplicate Rows Based On Cell/column Values?

Oct 28, 2009

I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:

If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.

View 5 Replies View Related

Deleting Duplicate Values In A Row

Aug 22, 2006

My excel looks like this. The rows have duplicate names which I want to delete:
TimeNamesNamesNamesNamesNamesNames
5 hoursBobTimTonyBobTom
7 HoursLisaPamPamHeatherKimKim
6 HoursLisaTimBobTimTim

I want the excel to look like this:
TimeNamesNamesNamesNames
5 hoursBobTimTonyTom
7 HoursLisaPamHeatherKim
6 HoursLisaTimBob

View 6 Replies View Related

Finding And Deleting Both Duplicate Rows

Aug 5, 2007

I deal with leads for a sales room and get sent over leads in bulk, I've created a master scrub list that I can attach to the end of a new lead file and sort by number to show which are duplicates.

When you do the: Data, Filter, Advanced Filter, select Unique Records, it hides the duplicate but what I need is not only for the duplicate to be hidden or gone but the row that it is a duplicate of, i.e. I need BOTH rows to go

Name-----number
Dave 555-1212
Dave 555-1212
John 536-2343
Smith 423-2312

needs to become

Name-----number
John 536-2343
Smith 423-2312

I would need a formula that figured out that Dave with number 555-1212 was a duplicate and delete BOTH rows,

View 10 Replies View Related

Deleting Duplicate Rows When ALL Columns Are Identical

Aug 8, 2007

I found a useful resource on the web that gives a macro that deletes rows when the cells within a SINGLE column are identical. [url]

Does anyone know of a macro that can do more than that, one that will delete rows if ALL cells within ALL columns are identical?

For example, the macro should delete row 4 of the attached spreadsheet. It should leave row #2 there because it's the orginal row. But the duplicate row #4 should be deleted. The macro should leave row #5 there because not all columns are identical for that row.

View 9 Replies View Related

Deleting Duplicate Rows :: Compare 2 Cells Per Row

Sep 22, 2008

I have a spreadsheet that I need to delete duplicate rows in. However, in order to determine if a row is a duplicate I need to check 2 cells per row. In the attached file you will see that each row has 4 cells. I need to compare the cells in columns B and C with the B and C cells of the Row beneath. If the B and C cells match then it is considered a duplicate and one of the rows needs to be deleted. Also, there may be multiple duplicate rows.

For an example see rows 17, 18 and 19. I only need 1 row to remain.

I am looking for a vb script that would analyze a file with thousands of rows and delete the duplicates.

View 12 Replies View Related

Deleting Duplicate Cells (not Entire Rows)

Dec 17, 2007

I have a long list (over 1000 lines) of numbers, some of which are duplicated. Up to now I have been sorting them, adding in a simple check column to see if the number is a duplicate of the one below, and then manually deleting this cell.

Is there a better way to do this using VBA? The only way I have managed to find is by deleting the entire row, but I cannot do this as I have additional data to the right that I need to keep.

I also cannot install any add-ons as this on a work computer.

View 9 Replies View Related

Deleting Duplicate Rows :: Without Including Recent

Jan 16, 2008

I have many rows that contain some common data but I need to be able to select the row with the most recent data and delete the others. For example, a car part is assigned a unique number which never changes, it may be ordered several times and in different quantities, for each car part I want to select the most recent order and delete any others. Is there a simple way of doing this?

View 9 Replies View Related

Deleting Duplicate Rows Based On Earliest Or Latest Time?

Mar 19, 2014

Below is an example of an Excel sheet I'm working with: [URL]

Basically, I'm trying to delete the duplicate rows by matching ID, Date and Type. If ID, Date and Type are the same, then, I want to only keep the record with the earliest Time in case of Type = In and the latest Time in case of Type = Out.

So, for example, in the case of ID = 1, there are 3 records for In, I only want to keep the one where Time is: 8:01 as this is the earliest. The other 2 records should be deleted.

Similarly, in the case of ID 3, I want to keep the record where Time = 18:05 as this is the later time out of the 2.

Can this be achieved by Conditional Formatting or Macro or VBA?

View 1 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.

EXAMPLE:
BEFORE
A B C D E F G H
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

AFTER
A B C D E F G H
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

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.

Col A
100
101
102
105
100
101
102
105

Col D
5
4
2
4
1
2
3
1

After my code is run, I need for my spreadsheet to look like this

Col A
100
101
102
105

Col D
6
6
5
5

I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.

Here is my code thus far....

-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)

[Code]....

View 9 Replies View Related

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.

Col A
100
101
102
105
100
101
102
105

Col D
5
4
2
4
1
2
3
1


After my code is run, I need for my spreadsheet to look like this
Col A
100.........................

View 9 Replies View Related

Filling Rows According To Cell Values And Deleting Rows With No Values

Jan 25, 2013

I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:

1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?

I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.

Test Tracking Sheet.xlsx‎

View 4 Replies View Related

Deleting Rows With Certain Values In VBA

Apr 8, 2009

I'm running into an issue when I try to generate a report for the largest five values in a report. Some of the cells have a zero divisor in their formula so they come up with the #DIV/0 error. I've put in an If statement to make those equal 0, but the LARGE formula still returns #NAME?. Any ideas on how to get the LARGE formula to work.

I would like to be able to delete the rows with zero in the reference column, so their data is not even in the report. Does anyone know how to do this in VBA?

View 9 Replies View Related

Deleting Rows With Non-percentage Values

Jun 9, 2009

What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.

What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....

View 6 Replies View Related

Copy Values And Deleting Rows VBA?

Nov 13, 2012

I've tried to write a VBA code that copies a value down and deletes empty rows. The macro needs to copy down the value in column A from the above row if the current column is empty but delete a row if column B is empty. The following code will copy down the value but will trigger a 400 error and won't delete any rows. The desired effect is to have column A completely filled and the empty row (Row 5) and the row containing "Page 1" (Row 6) deleted.

Code:
Public Sub fillType()
Dim LastRow As Integer
Dim Count As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For Count = 0 To LastRow - 1
'If A column is empty, copy value from column above

[code].....

View 2 Replies View Related

VBA - Deleting Rows With Blank Values

Nov 13, 2006

I am trying to write a macro which will delete all rows where cells in column B are blank. I am new to macros and since I have only been able to record them, I am having a tough time hand-writing this one.

View 9 Replies View Related

Deleting Rows Where Values Are Opposite & Equal

Mar 31, 2008

I have a spreadsheeet with values in columns F:K from row 10 onwards

I need VBA code that will delete the rows where there are values opposite and equal in columns F:K from row 10 owards for eg if F10 is 10000 and K10 is -10000, F 25 is 150000 and G25 is -10000, G29 is -175000 and H29 is 175000, then rowa 10,25 & 29 must be deleted

View 9 Replies View Related

Delete Duplicate Rows Where Column A And Column B Combined Are Equal

Dec 8, 2009

I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.

View 2 Replies View Related

Marking Rows With Certain Duplicate Values?

Jul 8, 2014

So I am trying to mark certain rows as duplicates if their values in Columns J, AD and ABS(BD) match. J and AD contain Number letter Identification combinations and BD contains a value. I currently have a formula as shows that will mark all of these values:

[Formula]
=IF(AND(COUNTIFS(J:J,J15,BD:BD,ABS(BD15),AD:AD,AD15)>1, (SUMIFS(BD:BD,J:J,J15,AD:AD,AD15,BD:BD,ABS(BD15)))<>ABS(BD15)),"Duplicate","")
[Formula]

The issue with this formula is that I do not want it to mark rows that look like this:

J - AD - BD
AA1 BB2 20
AA1 BB2 -20
AA1 BB2 20

I have a lot of rows that will appear like this. Their sum in BD equals the absolute value of the value in BD. I thought this would get picked up using the SUMIFS portion of my formula but it is not. I cannot find the problem.

View 2 Replies View Related

Delete Rows With Duplicate Values

May 1, 2006

I have a spreadsheet with three colums of data. The first column contains
records which have occasional phone number duplication- see blelow:

(555) 000-0000DataA1ValueA1
(555) 000-0000DataA2ValueA2
(555) 555-9770DataA3ValueA3
(555) 555-4464DataA4ValueA4
(555) 555-4464DataA5ValueA5
(555) 555-4720DataA6ValueA6
(555) 555-8823DataA7ValueA7
(555) 555-3834DataA8ValueA8
(555) 555-4125DataA9ValueA9

What I need to do is (somehwhat) automate the process of filtering or
deleting out all rows which have duplicate data in the first column, but not
second or third columns. I'm sure it's been done...I tried the Excel
out-of-the-box.

View 14 Replies View Related

Copy All Rows Between 2 Duplicate Values

Jun 21, 2014

copying all Rows(including blanks) between 2 identical rows

for example

ID
Notes

4554
asdf

--
asf

[code]....

Copy from 2nd row to 7th row (I.E 4554 - 4554) 4554 is on sheet 2 with other rep ID's - Loop with other ID's would be great

View 8 Replies View Related

Searching A Column For A Word And Deleting All Rows That Don't Contain It

Jun 21, 2007

I am trying to create a search function in a database I have made. I have a userform with drop down boxes that people can choose what they want to search for. Once they have chosen and clicked the search button I want it to search only in the relevant column, i.e. search for the word in the dropdown for "engine" only in the "engine" column of the database. Once it has found the appropriate cells with the answer, I want it to delete all other rows that don't include the answer.

Once it's done this it needs to do it again for the other fields, unless they are empty. I have used 'If engine_type <> "" Then' as the starting point so that it only searches chosen fields.

View 4 Replies View Related

Duplicate Values Multiple Rows To Be Highlighted?

Jan 8, 2013

how to highlight in one rows and 3 column if there is a duplicate.. i have attached here as an example....NameMatch.xlsx

View 4 Replies View Related

Duplicate Rows Based On SUM Of Cell Values

Mar 1, 2008

I have a number of rows that I want to have duplicated X number of times (and altered) where X is found by looking at certain cells within each row.

There are four numbers in each row, and I want to split them up into multiple rows each with three zeros and one one.

I would like to convert data from this:

Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 3 0 1 0
Mike 0 1 1 2
etc.

...to this:

Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 0 0 1 0
Mike 0 1 0 0
Mike 0 0 1 0
Mike 0 0 0 1
Mike 0 0 0 1
etc.

You can see that the W, X, Y, and Z columns from the four new Mike rows sum to equal the values in the original Mike row (0, 1, 1, 2), but everything has been split so that each row just has a single one in it and three zeros.

Does anyone have an idea of how to do this? Thanks Auto Merged Post Until 24 Hrs Passes;I thought of another way of putting it that may be easier to understand.

Given an input row of "George, 4, 7, 3, 2", I would like the output to contain 4 rows of "George, 1, 0, 0, 0", 7 rows of "George, 0, 1, 0, 0", 3 rows of "George, 0, 0, 1, 0", and 2 rows of "George, 0, 0, 0, 1".

View 3 Replies View Related







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