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.

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.

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
should consider downloading the 'Asap Utilities' a free excel add-on with
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.

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,

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.

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.

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.