Consolidate Duplicate Data And Then Delete

Jan 29, 2008

Here is a sample Excel sheet I have: http://spreadsheets.google.com/pub?k...dnD01VPV8RzOAw

I have over 1,200 rows with duplicate data(sometims 3-4 times) that is filled in some columns and empty in others.

I would like to consolidate the data and then delete the duplicates without loosing any data.

Is there a way to do this? I have Office 2007. I can't just use the Duplicate finder cause that just deletes everything right away.

View 9 Replies


ADVERTISEMENT

Consolidate Matching Rows And Delete Duplicate Rows?

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.

My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.

So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5

[Code]....

My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.

View 4 Replies View Related

Consolidate Matching Rows And Delete Duplicate Rows

Feb 20, 2008

I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:

Project | Task | Name | Role | Date | Hours

123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5

Should be processed and come out like this:.......................

View 2 Replies View Related

Consolidate Duplicate Fields / Records?

Apr 12, 2013

I have spent the last couple hours compiling a large amount of data. I have a list of websites, each have a number of visits and transactions. The list is many thousands strong so i have a huge amount of data to work with but there are a lot of duplicate sites. See below, I have taken a snapshot of some of it:

I've highlighted a duplicate example.

Would it be possible across the entire data set to sum all of the duplicate rows into one. So, for the 3 shown, to be reduced to one row with 228 visits and 2 transactions?

And to do it quickly? As I could go through them manually but I don't have to time to go through 10,000 rows.

View 4 Replies View Related

Consolidate Multiple Spreadsheets (consolidate All The Data)

Oct 17, 2008

I have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.

Here is what I have:

Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.

I have 25 users with 25 seperate workbooks each with individual information on each workbook.

I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.

The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.

I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.

View 2 Replies View Related

Consolidate Macro Elimination Duplicate Statements

Dec 21, 2009

how this code can be condensed getting rid of some of the duplicate coding?

Everything is duplicate except for the For i = 1 To x part as the formula that is added is changed from sheet to sheet.

View 10 Replies View Related

Delete Duplicate Data ...

May 16, 2009

I'm a first timer here an I'm hving a problem with data. I have 3 sheets in a workbook, I want to transfer all the data to one sheet. I have 3 columns labelled Number, Name, & Sales, on each sheet some of the numbers and names are the same and I want to be able to match them up and put the sales from each sheet into a new column, so the final sheet will have 5 columns in total, if the numbers and names don"t match I just want to add those to the bottom of the matched ones.

View 10 Replies View Related

Delete Duplicate And Merge Data In One Row

Sep 27, 2008

I have a new project that needs macro code. Your help is very much appreciated. We have a spreadsheet with duplicate accounts meaning two or three rows with the same account but different information. We want to use only one row for one account and move the new data from the same account to one row only to the right and delete the duplicates. Can someone please help me with this?I read so many post and I tried some of them but it only delete the duplicate row and not copying the new data from that row to one row only. Also, the other code I tried was retaining only the current or old data. Actually, to elaborate more, I want to get the new data in each cell of the same account in multiple row and move it in one row to the right only and delete the duplicate in that same account.

View 14 Replies View Related

How To Find Duplicate Data And Delete Row

Mar 29, 2012

I found a lot of information on this but not what I need. I have 8 columns A - H. Column D has some duplicate numbers. I would like to find the duplicate numbers in column D (they are all one right after the other) and delete the entire row leaving only the first. I do not need to sum or anything, just delete the row with a duplicate number. If there are 2 or 3, I just end up with one.

View 2 Replies View Related

Macro Delete Duplicate Data

Oct 27, 2008

I have an Excel Workbook with a Paid sheet and an Outstanding sheet both sheets have a File Number column.

I would like to create a macro that looks through the Outstanding sheet and if the File Number from the Outstanding sheet is also in the Paid sheet can the entire row be deleted from the Outstanding sheet.

View 9 Replies View Related

Find Duplicate Data And Delete Row

May 25, 2006

compare and find exact matches b/w the data in columns J and K in the attached csv file. I would simply like all rows with exact matches to be hidden. I can simply change any code to reflect something like

Selection.EntireRow.delete

later if necessary.

View 3 Replies View Related

Count & Delete Duplicate Data

May 30, 2007

macro that takes the number data and if it is the same number as in the previous row(s) just delete the extra number(s). It also needs to put in a factor in the next column of how many times the number was the same. For example:

Number Factor
5.67 1
5.70 1
5.77 1
5.77 1
5.77 1
5.77 1

Would become

Number Factor
5.67 1
5.70 1
5.77 4

I tried to make this question as clear as possible.

View 9 Replies View Related

Delete Duplicate Rows Data In Only One Column

Jul 28, 2013

with deleting duplicate entries in a spreadsheet that contains two names, a code number and a date. All rows with a duplicate number in column “B” should be deleted. Write a micro code? I have a few thousand of entries that need to be knocked out.

A sample file is attached : Example.xls

View 5 Replies View Related

Select Column And Delete Row Where Data Is Duplicate

Nov 17, 2006

I've got this macro which works well, but I have to edit it to change the column I want to check for dupes. I don't mind doing that, but now I have to share the macro with someone who is not comfortable changing the references in the code every time they run it.

As it is it checks column G:G (7) for dupes and deletes the row. Good.
I'd like for it to do the same thing, but for whatever column the active cell is in.

I can use this:
Col = ActiveCell.Column
in place of the "7" in the With Range part
but I don't know how to replace the "G1:G".

Sub DeleteDuplicatesColumnG()
With Application
.ScreenUpdating = False
.EnableEvents = False

View 9 Replies View Related

Delete Duplicate Rows Using Data In Column A As Criteria

Jan 13, 2009

I have a spreadsheet that has thousands of rows of data with maybe 6 columns.

I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.

There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.

Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg

So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1

I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.

View 2 Replies View Related

Concat Rows Based On Duplicate Data & Delete

Jun 18, 2009

I need to concatenate and de-dupe. I want to find dupes, concatenate a column, and the delete the duplicate columns--all while maintaining data from other populated columns.

Here is the some example data:

#| Type | Name
1 | A | Bob
1 | B | Bob
1 | C | Bob
2 | B | Sally
2 | E | Sally
3 | A | Bill
3 | C | Bill
4 | E | Nancy

The result should look like:

# | Type | Name
1 | A, B, C | Bob
2 | B, E | Sally
3 | A, C | Bill
4 | E | Nancy

I am using Excel 2003.

View 5 Replies View Related

Excel 2010 :: Delete Row With Duplicate Data In 2 Columns?

Apr 21, 2014

Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.

View 3 Replies View Related

Macros To Delete Entire Duplicate Row For Duplicate Values?

Aug 19, 2014

I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.

Copy of OCL 2010 (3).xlsx‎

View 1 Replies View Related

Delete Duplicate Rows :: Duplicate Company Names

Dec 11, 2008

I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.

Attached is a sample of that spreadsheet.

View 5 Replies View Related

Delete Duplicate Cells Or Rows With A Duplicate Cell

Nov 1, 2007

I feel as though I have spent enough time searching the previous posts to ask this question.

I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.

COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner

I want to end up with rows that each contain unique info in COLUMN B.

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
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

Delete Duplicate Entries, Keep Last And Delete First

Feb 15, 2010

I found this sample code that works from top to bottom of a spreadsheet. But I need something that will delete the first entry and keep the last entry. My data is sent from one spreadsheet to a Master and sometimes the details can be sent twice, if the responsible person forgets to enter one line of production. The criteria should be the first 5 Columns of the sheet.

Sub Dupe_Killer()
Dim str As String
Dim str2 As String
Dim c As Integer
Dim i As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("SAMPLE").Select
rw = Cells(2, 1).End(xlDown).Row
'Sort Data by Date, Location & Number
Range(Cells(1, 1), Cells(1, 14)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2) _
, Order2:=xlAscending, Key3:=Cells(1, 3), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ....................................

View 2 Replies View Related

Data Consolidate - How To Maintain Case Sensitive Data After Merging Cells

Dec 8, 2013

I'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.

Check out this simple table as an example >

A
B
C
D
E
F
G
H

1
TEXT
QTY
TEXT
QTY
TEXT
QTY
TEXT
QTY

2
and
3
and
2
and
10
and
5

[Code] .........

Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.

Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.

Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.

View 3 Replies View Related

Delete Duplicate Value

Jan 27, 2007

i've got a range of data. Typically there are columns that have the same value running for a few hundred lines before it gets to the next value. What i'm trying to do is create a macro that when i select a value in a column i would run the macro and it would delete all the duplicate values until it reaches the next value but making sure it only deletes that value in that specific column.

View 3 Replies View Related

Delete Duplicate Records

Jul 15, 2009

I have a spreadsheet with a column of records (text ) from B1:B4000. I would like to delete the entire row of the duplicate record. Overall data set range is (A1:I4000). This spreadsheet may contain more than 1 duplicate record. Is there in easy fix?

View 12 Replies View Related

Sum, Merge, And Delete Duplicate Row

Sep 24, 2009

I've been trying to sum merge, and delete duplicate rows in this massive spreadsheet (over 15,000 rows). Currently I've gotten to the point where the duplicate lines are eliminated but the quantity is recording the number of times the line has moved, not summing. Below is an example of what I'm looking for

EX:..............

View 3 Replies View Related

Delete Of Duplicate Records

Nov 22, 2009

I'm having trouble getting this piece of macro to work. I want to delete duplicate Id records in (Cell B12 : B124), but only those with "No" in the (Cell Z12 : Z124) will be deleted.

Table
B12 Z12
-------------
E1 Yes
E2 Yes
E3 Yes
E4 Yes
E1 No
E2 No
E3 No
E4 No

Expected results:
E1 Yes
E2 Yes
E3 Yes
E4 Yes

View 13 Replies View Related

Delete All But Highest Value Duplicate?

Oct 1, 2011

I have 2 columns A & B. A has many duplicates and B has numerical values. I would like to delete all duplicates in A apart from the ones highest value in B?

View 9 Replies View Related

VBA To Delete Duplicate Rows?

Aug 18, 2012

VBA code that will delete all the rows where cell "C" is duplicated

I have look on the net and there is loads of complicated codes out there but I am looking for something fast.

View 2 Replies View Related







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