# Delete duplicate rows of data when two columns are the same

Katerinia
Guest
Posts: n/a

 15th Apr 2010
I have a worksheet and need to delete duplicate rows when two of my columns
are the same.

So as below:
For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are
the same, look at the "MASTER COST CENTER" column and see if thats the
cooresponding rows are the same. If yes, delete that record (row). If no,
leave it.
T U W
X
1 Job ADP EMPLOYEE Master Cost Center Strd Hours
2 123500 111111111 98140 40
3 123500 111111111 98140 37.5
4 409997 333333333 17280 40
5 409997 444444444 16582 40
6 409997 666666666 17275 37.5
7 409997 666666666 17280 40
----------------------------------------------------------------------------
example: should look like this

Job ADP EMPLOYEE Master Cost Center Strd Hours
123500 111111111 98140 40
409997 333333333 17280 40
409997 444444444 16582 40
409997 666666666 17275 37.5
409997 666666666 17280 40

Hope you can help, I am nearing the end of a very long project and would
appreciate this so much!
--
Excel isnt just a program its a metaphor for life. Sometimes it''''s easy
and boring.. sometimes it''''s a little challenging and great.. then there
are days it will drive you absolutely INSANE.

Mike H
Guest
Posts: n/a

 15th Apr 2010
Hi,

Questions.

Is it always the first row of any duplicates you want to keep?
Will there ever be more than 2 duplicate rows?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"Katerinia" wrote:

> I have a worksheet and need to delete duplicate rows when two of my columns
> are the same.
>
> So as below:
> For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are
> the same, look at the "MASTER COST CENTER" column and see if thats the
> cooresponding rows are the same. If yes, delete that record (row). If no,
> leave it.
> T U W
> X
> 1 Job ADP EMPLOYEE Master Cost Center Strd Hours
> 2 123500 111111111 98140 40
> 3 123500 111111111 98140 37.5
> 4 409997 333333333 17280 40
> 5 409997 444444444 16582 40
> 6 409997 666666666 17275 37.5
> 7 409997 666666666 17280 40
> ----------------------------------------------------------------------------
> example: should look like this
>
> Job ADP EMPLOYEE Master Cost Center Strd Hours
> 123500 111111111 98140 40
> 409997 333333333 17280 40
> 409997 444444444 16582 40
> 409997 666666666 17275 37.5
> 409997 666666666 17280 40
>
>
> Hope you can help, I am nearing the end of a very long project and would
> appreciate this so much!
> --
> Excel isnt just a program its a metaphor for life. Sometimes it''''s easy
> and boring.. sometimes it''''s a little challenging and great.. then there
> are days it will drive you absolutely INSANE.

Reg
Guest
Posts: n/a

 15th Apr 2010
Hi

befoere you start make sure you really know which record you want to delete
- in the example you got rid of the one with fewer standard hours - is that
always the case?

Excel 2007 includes a duplicate row removal feature and prior to that you
lots of stuff like this.

In the meantime try the following:

create a column at the end of your data set as follows:

Column AA: =Jobadp&employee&master cost centre (use the correct columns/rows
for your sheet - from the example it would be =T2 & U2 & W2)
fill this down to the bottom of your data

Column AB: = countif(\$AA:\$AA,AA2) - the AA2 should reflect the first row of
your data (2 in the example)

- when you recalculate column AB will show a number greater than 1 for every
row you want to delete. Either manually scan down and delete those greater
than 1 or :-

Select all the values in column AB and 'copy', then 'paste special, values'
into column AC - make sure the rows match up exactly. Now select the ENTIRE
table including all your data upto AC and sort on AC - find the first row in
AC that is greater then 1 and delete the rest.

simple huh?

regards
Reg

"Katerinia" wrote:

> I have a worksheet and need to delete duplicate rows when two of my columns
> are the same.
>
> So as below:
> For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are
> the same, look at the "MASTER COST CENTER" column and see if thats the
> cooresponding rows are the same. If yes, delete that record (row). If no,
> leave it.
> T U W
> X
> 1 Job ADP EMPLOYEE Master Cost Center Strd Hours
> 2 123500 111111111 98140 40
> 3 123500 111111111 98140 37.5
> 4 409997 333333333 17280 40
> 5 409997 444444444 16582 40
> 6 409997 666666666 17275 37.5
> 7 409997 666666666 17280 40
> ----------------------------------------------------------------------------
> example: should look like this
>
> Job ADP EMPLOYEE Master Cost Center Strd Hours
> 123500 111111111 98140 40
> 409997 333333333 17280 40
> 409997 444444444 16582 40
> 409997 666666666 17275 37.5
> 409997 666666666 17280 40
>
>
> Hope you can help, I am nearing the end of a very long project and would
> appreciate this so much!
> --
> Excel isnt just a program its a metaphor for life. Sometimes it''''s easy
> and boring.. sometimes it''''s a little challenging and great.. then there
> are days it will drive you absolutely INSANE.

Katerinia
Guest
Posts: n/a

 15th Apr 2010
yes, the stnd hours is data that was just shoved in there and not accurate,
they have to reenter that data. So the first record is fine.

there could be more than two duplicates yes.
--
Excel isnt just a program its a metaphor for life. Sometimes it is easy and
boring.. sometimes it is a little challenging and great.. then there are days
it will drive you absolutely INSANE.

"Mike H" wrote:

> Hi,
>
> Questions.
>
> Is it always the first row of any duplicates you want to keep?
> Will there ever be more than 2 duplicate rows?
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Katerinia" wrote:
>
> > I have a worksheet and need to delete duplicate rows when two of my columns
> > are the same.
> >
> > So as below:
> > For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are
> > the same, look at the "MASTER COST CENTER" column and see if thats the
> > cooresponding rows are the same. If yes, delete that record (row). If no,
> > leave it.
> > T U W
> > X
> > 1 Job ADP EMPLOYEE Master Cost Center Strd Hours
> > 2 123500 111111111 98140 40
> > 3 123500 111111111 98140 37.5
> > 4 409997 333333333 17280 40
> > 5 409997 444444444 16582 40
> > 6 409997 666666666 17275 37.5
> > 7 409997 666666666 17280 40
> > ----------------------------------------------------------------------------
> > example: should look like this
> >
> > Job ADP EMPLOYEE Master Cost Center Strd Hours
> > 123500 111111111 98140 40
> > 409997 333333333 17280 40
> > 409997 444444444 16582 40
> > 409997 666666666 17275 37.5
> > 409997 666666666 17280 40
> >
> >
> > Hope you can help, I am nearing the end of a very long project and would
> > appreciate this so much!
> > --
> > Excel isnt just a program its a metaphor for life. Sometimes it''''s easy
> > and boring.. sometimes it''''s a little challenging and great.. then there
> > are days it will drive you absolutely INSANE.

Mike H
Guest
Posts: n/a

 15th Apr 2010
Katerinia,

Try this macro. your original post wasn't very clear because of line-wrap
but I think the 2 columns were interested in are T & W.

If that's incorrect then alter column T in these lines to the correct one
LastRow = sht.Cells(Cells.Rows.Count, "T").End(xlUp).Row
Set MyRange = sht.Range("T3:T" & LastRow)

I get the second column as an offset from column T in this line
If c = c.Offset(-1) And c.Offset(, 3) = c.Offset(-1, 3) Then

Column W is offset 3 from column T so if that's wrong change the 3 but NOT
the -1

Sub stance()
Dim MyRange As Range
Dim CopyRange As Range
Dim LastRow As Long
Dim c As Range
Set sht = Sheets("Sheet1") ' Change to suit
LastRow = sht.Cells(Cells.Rows.Count, "T").End(xlUp).Row
Set MyRange = sht.Range("T3:T" & LastRow)
For Each c In MyRange
If c = c.Offset(-1) And c.Offset(, 3) = c.Offset(-1, 3) Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"Katerinia" wrote:

> yes, the stnd hours is data that was just shoved in there and not accurate,
> they have to reenter that data. So the first record is fine.
>
> there could be more than two duplicates yes.
> --
> Excel isnt just a program its a metaphor for life. Sometimes it is easy and
> boring.. sometimes it is a little challenging and great.. then there are days
> it will drive you absolutely INSANE.
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Questions.
> >
> > Is it always the first row of any duplicates you want to keep?
> > Will there ever be more than 2 duplicate rows?
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "Katerinia" wrote:
> >
> > > I have a worksheet and need to delete duplicate rows when two of my columns
> > > are the same.
> > >
> > > So as below:
> > > For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are
> > > the same, look at the "MASTER COST CENTER" column and see if thats the
> > > cooresponding rows are the same. If yes, delete that record (row). If no,
> > > leave it.
> > > T U W
> > > X
> > > 1 Job ADP EMPLOYEE Master Cost Center Strd Hours
> > > 2 123500 111111111 98140 40
> > > 3 123500 111111111 98140 37.5
> > > 4 409997 333333333 17280 40
> > > 5 409997 444444444 16582 40
> > > 6 409997 666666666 17275 37.5
> > > 7 409997 666666666 17280 40
> > > ----------------------------------------------------------------------------
> > > example: should look like this
> > >
> > > Job ADP EMPLOYEE Master Cost Center Strd Hours
> > > 123500 111111111 98140 40
> > > 409997 333333333 17280 40
> > > 409997 444444444 16582 40
> > > 409997 666666666 17275 37.5
> > > 409997 666666666 17280 40
> > >
> > >
> > > Hope you can help, I am nearing the end of a very long project and would
> > > appreciate this so much!
> > > --
> > > Excel isnt just a program its a metaphor for life. Sometimes it''''s easy
> > > and boring.. sometimes it''''s a little challenging and great.. then there
> > > are days it will drive you absolutely INSANE.

Katerinia
Guest
Posts: n/a

 15th Apr 2010
Actually Colomns T, U and W are the important ones.

For each SS#(EMPLOYEE) in Column U, i need to check T and W to see if any
rows match. If all three do, then delete.

Thanks for your help on this.. Hope this explains it more.
--
Excel isnt just a program its a metaphor for life. Sometimes it is easy and
boring.. sometimes it is a little challenging and great.. then there are days
it will drive you absolutely INSANE.

"Mike H" wrote:

> Katerinia,
>
> Try this macro. your original post wasn't very clear because of line-wrap
> but I think the 2 columns were interested in are T & W.
>
> If that's incorrect then alter column T in these lines to the correct one
> LastRow = sht.Cells(Cells.Rows.Count, "T").End(xlUp).Row
> Set MyRange = sht.Range("T3:T" & LastRow)
>
> I get the second column as an offset from column T in this line
> If c = c.Offset(-1) And c.Offset(, 3) = c.Offset(-1, 3) Then
>
> Column W is offset 3 from column T so if that's wrong change the 3 but NOT
> the -1
>
>
> Sub stance()
> Dim MyRange As Range
> Dim CopyRange As Range
> Dim LastRow As Long
> Dim c As Range
> Set sht = Sheets("Sheet1") ' Change to suit
> LastRow = sht.Cells(Cells.Rows.Count, "T").End(xlUp).Row
> Set MyRange = sht.Range("T3:T" & LastRow)
> For Each c In MyRange
> If c = c.Offset(-1) And c.Offset(, 3) = c.Offset(-1, 3) Then
> If CopyRange Is Nothing Then
> Set CopyRange = c.EntireRow
> Else
> Set CopyRange = Union(CopyRange, c.EntireRow)
> End If
> End If
> Next
> If Not CopyRange Is Nothing Then
> CopyRange.Delete
> End If
> End Sub
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Katerinia" wrote:
>
> > yes, the stnd hours is data that was just shoved in there and not accurate,
> > they have to reenter that data. So the first record is fine.
> >
> > there could be more than two duplicates yes.
> > --
> > Excel isnt just a program its a metaphor for life. Sometimes it is easy and
> > boring.. sometimes it is a little challenging and great.. then there are days
> > it will drive you absolutely INSANE.
> >
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > Questions.
> > >
> > > Is it always the first row of any duplicates you want to keep?
> > > Will there ever be more than 2 duplicate rows?
> > > --
> > > Mike
> > >
> > > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > > introduces the fewest assumptions while still sufficiently answering the
> > > question.
> > >
> > >
> > > "Katerinia" wrote:
> > >
> > > > I have a worksheet and need to delete duplicate rows when two of my columns
> > > > are the same.
> > > >
> > > > So as below:
> > > > For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are
> > > > the same, look at the "MASTER COST CENTER" column and see if thats the
> > > > cooresponding rows are the same. If yes, delete that record (row). If no,
> > > > leave it.
> > > > T U W X
> > > > 1 Job ADP EMPLOYEE Master Cost Center Strd Hours
> > > > 2 123500 111111111 98140 40
> > > > 3 123500 111111111 98140 37.5
> > > > 4 409997 333333333 17280 40
> > > > 5 409997 444444444 16582 40
> > > > 6 409997 666666666 17275 37.5
> > > > 7 409997 666666666 17280 40
> > > > ----------------------------------------------------------------------------
> > > > example: should look like this
> > > >
> > > > Job ADP EMPLOYEE Master Cost Center Strd Hours
> > > > 123500 111111111 98140 40
> > > > 409997 333333333 17280 40
> > > > 409997 444444444 16582 40
> > > > 409997 666666666 17275 37.5
> > > > 409997 666666666 17280 40
> > > >
> > > >
> > > > Hope you can help, I am nearing the end of a very long project and would
> > > > appreciate this so much!
> > > > --
> > > > Excel isnt just a program its a metaphor for life. Sometimes it''''s easy
> > > > and boring.. sometimes it''''s a little challenging and great.. then there
> > > > are days it will drive you absolutely INSANE.

Mike H
Guest
Posts: n/a

 15th Apr 2010
Katerinia,

This now check columns T, U and W and if they are the same then as the
previous rows(s) then only the top record is retained, the rest are deleted.
One thing I never considered is that in your test data column T is sorted and
I have assumed this will always be the case.

Dim MyRange As Range
Dim CopyRange As Range
Dim LastRow As Long
Dim c As Range
Set sht = Sheets("Sheet1") ' Change to suit
LastRow = sht.Cells(Cells.Rows.Count, "T").End(xlUp).Row
Set MyRange = sht.Range("T3:T" & LastRow)
For Each c In MyRange
If c = c.Offset(-1) And c.Offset(, 3) = c.Offset(-1, 3) And _
c.Offset(, 1) = c.Offset(-1, 1) Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.delete
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"Katerinia" wrote:

> Actually Colomns T, U and W are the important ones.
>
> For each SS#(EMPLOYEE) in Column U, i need to check T and W to see if any
> rows match. If all three do, then delete.
>
> Thanks for your help on this.. Hope this explains it more.
> --
> Excel isnt just a program its a metaphor for life. Sometimes it is easy and
> boring.. sometimes it is a little challenging and great.. then there are days
> it will drive you absolutely INSANE.
>
>
> "Mike H" wrote:
>
> > Katerinia,
> >
> > Try this macro. your original post wasn't very clear because of line-wrap
> > but I think the 2 columns were interested in are T & W.
> >
> > If that's incorrect then alter column T in these lines to the correct one
> > LastRow = sht.Cells(Cells.Rows.Count, "T").End(xlUp).Row
> > Set MyRange = sht.Range("T3:T" & LastRow)
> >
> > I get the second column as an offset from column T in this line
> > If c = c.Offset(-1) And c.Offset(, 3) = c.Offset(-1, 3) Then
> >
> > Column W is offset 3 from column T so if that's wrong change the 3 but NOT
> > the -1
> >
> >
> > Sub stance()
> > Dim MyRange As Range
> > Dim CopyRange As Range
> > Dim LastRow As Long
> > Dim c As Range
> > Set sht = Sheets("Sheet1") ' Change to suit
> > LastRow = sht.Cells(Cells.Rows.Count, "T").End(xlUp).Row
> > Set MyRange = sht.Range("T3:T" & LastRow)
> > For Each c In MyRange
> > If c = c.Offset(-1) And c.Offset(, 3) = c.Offset(-1, 3) Then
> > If CopyRange Is Nothing Then
> > Set CopyRange = c.EntireRow
> > Else
> > Set CopyRange = Union(CopyRange, c.EntireRow)
> > End If
> > End If
> > Next
> > If Not CopyRange Is Nothing Then
> > CopyRange.Delete
> > End If
> > End Sub
> >
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "Katerinia" wrote:
> >
> > > yes, the stnd hours is data that was just shoved in there and not accurate,
> > > they have to reenter that data. So the first record is fine.
> > >
> > > there could be more than two duplicates yes.
> > > --
> > > Excel isnt just a program its a metaphor for life. Sometimes it is easy and
> > > boring.. sometimes it is a little challenging and great.. then there are days
> > > it will drive you absolutely INSANE.
> > >
> > >
> > > "Mike H" wrote:
> > >
> > > > Hi,
> > > >
> > > > Questions.
> > > >
> > > > Is it always the first row of any duplicates you want to keep?
> > > > Will there ever be more than 2 duplicate rows?
> > > > --
> > > > Mike
> > > >
> > > > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > > > introduces the fewest assumptions while still sufficiently answering the
> > > > question.
> > > >
> > > >
> > > > "Katerinia" wrote:
> > > >
> > > > > I have a worksheet and need to delete duplicate rows when two of my columns
> > > > > are the same.
> > > > >
> > > > > So as below:
> > > > > For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are
> > > > > the same, look at the "MASTER COST CENTER" column and see if thats the
> > > > > cooresponding rows are the same. If yes, delete that record (row). If no,
> > > > > leave it.
> > > > > T U W X
> > > > > 1 Job ADP EMPLOYEE Master Cost Center Strd Hours
> > > > > 2 123500 111111111 98140 40
> > > > > 3 123500 111111111 98140 37.5
> > > > > 4 409997 333333333 17280 40
> > > > > 5 409997 444444444 16582 40
> > > > > 6 409997 666666666 17275 37.5
> > > > > 7 409997 666666666 17280 40
> > > > > ----------------------------------------------------------------------------
> > > > > example: should look like this
> > > > >
> > > > > Job ADP EMPLOYEE Master Cost Center Strd Hours
> > > > > 123500 111111111 98140 40
> > > > > 409997 333333333 17280 40
> > > > > 409997 444444444 16582 40
> > > > > 409997 666666666 17275 37.5
> > > > > 409997 666666666 17280 40
> > > > >
> > > > >
> > > > > Hope you can help, I am nearing the end of a very long project and would
> > > > > appreciate this so much!
> > > > > --
> > > > > Excel isnt just a program its a metaphor for life. Sometimes it''''s easy
> > > > > and boring.. sometimes it''''s a little challenging and great.. then there
> > > > > are days it will drive you absolutely INSANE.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Jennifer B. Microsoft Excel Misc 2 25th Jun 2008 03:47 PM Scott Microsoft Excel Programming 3 28th Dec 2007 01:07 AM JSteph Microsoft Excel Worksheet Functions 1 12th Dec 2007 02:59 AM =?Utf-8?B?Q2FycmllUg==?= Microsoft Access 3 30th Aug 2006 07:07 PM Vinit Microsoft Excel Programming 2 14th May 2005 04:20 AM

Features