very hard deleting rows issue

[Lynn Bales]

I have 4000 rows of data that looks like this:

Number Status
C02504 Removed
C02504 Removed
C02504 Removed
C02504 Removed
C02504 Removed
C02504 Removed
c02536 Removed
c02536 Added
c02536 Added
c02536 Not Changed
c02536 Not Changed
c02536 Not Changed
c02536 Not Changed
c02536 Not Changed
C02564 Not Changed
C03869 Removed
C03869 Added
C03869 Added

What I need to accomplish is to eliminate all but one row where the number
and status are the same but keep ALL the rows where the number has several
different statuses. So basically I need the above to look like:

Number Status
C02504 Removed
c02536 Removed
c02536 Added
c02536 Added
c02536 Not Changed
C02564 Not Changed
C03869 Removed
C03869 Added
C03869 Added

[David McRitchie]

You can do that without disturbing your data using Filters
Filter Unique Records (Debra Dalgleish)
http://www.contextures.com/xladvfilter01.html#FilterUR
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Lynn Bales" <[email protected]> wrote in message news:[email protected]...
> I have 4000 rows of data that looks like this:
>
> Number Status
> C02504 Removed
> C02504 Removed
> C02504 Removed
> C02504 Removed
> C02504 Removed
> C02504 Removed
> c02536 Removed
> c02536 Added
> c02536 Added
> c02536 Not Changed
> c02536 Not Changed
> c02536 Not Changed
> c02536 Not Changed
> c02536 Not Changed
> C02564 Not Changed
> C03869 Removed
> C03869 Added
> C03869 Added
>
> What I need to accomplish is to eliminate all but one row where the number
> and status are the same but keep ALL the rows where the number has several
> different statuses. So basically I need the above to look like:
>
> Number Status
> C02504 Removed
> c02536 Removed
> c02536 Added
> c02536 Added
> c02536 Not Changed
> C02564 Not Changed
> C03869 Removed
> C03869 Added
> C03869 Added
>

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim delRange As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
Cells(i, "B").Value = Cells(i - 1, "B").Value Then
If delRange Is Nothing Then
Set delRange = Cells(i, "A")
Else
Set delRange = Union(delRange, Cells(i, "A"))
End If
End If
Next i

If Not delRange Is Nothing Then
delRange.EntireRow.Delete
Set delRange = Nothing
End If

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lynn Bales" <[email protected]> wrote in message
news:[email protected]...
> I have 4000 rows of data that looks like this:
>
> Number Status
> C02504 Removed
> C02504 Removed
> C02504 Removed
> C02504 Removed
> C02504 Removed
> C02504 Removed
> c02536 Removed
> c02536 Added
> c02536 Added
> c02536 Not Changed
> c02536 Not Changed
> c02536 Not Changed
> c02536 Not Changed
> c02536 Not Changed
> C02564 Not Changed
> C03869 Removed
> C03869 Added
> C03869 Added
>
> What I need to accomplish is to eliminate all but one row where the number
> and status are the same but keep ALL the rows where the number has several
> different statuses. So basically I need the above to look like:
>
> Number Status
> C02504 Removed
> c02536 Removed
> c02536 Added
> c02536 Added
> c02536 Not Changed
> C02564 Not Changed
> C03869 Removed
> C03869 Added
> C03869 Added
>

[Lynn Bales]

Bob

This works part of the way. It deletes to much. I don't want it to delete
ANY lines for a number that has more than one status. I only need it to
delete extra rows when the number and the status match in every case.

So for a number that has 3 Adds and nothing else, delete the two extras. For
a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete ANY rows.
There is an extra column of data related to these two items I need to
preserve but don't want to include because it creates a unique entry for
EVERY row.

I've tried filtering and it also doesn't work correctly either.

Thanks so much!

Lynn

"Bob Phillips" wrote:

> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
> Dim delRange As Range
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = 2 To iLastRow
> If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
> Cells(i, "B").Value = Cells(i - 1, "B").Value Then
> If delRange Is Nothing Then
> Set delRange = Cells(i, "A")
> Else
> Set delRange = Union(delRange, Cells(i, "A"))
> End If
> End If
> Next i
>
> If Not delRange Is Nothing Then
> delRange.EntireRow.Delete
> Set delRange = Nothing
> End If
>
> End Sub
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lynn Bales" <[email protected]> wrote in message
> news:[email protected]...
> > I have 4000 rows of data that looks like this:
> >
> > Number Status
> > C02504 Removed
> > C02504 Removed
> > C02504 Removed
> > C02504 Removed
> > C02504 Removed
> > C02504 Removed
> > c02536 Removed
> > c02536 Added
> > c02536 Added
> > c02536 Not Changed
> > c02536 Not Changed
> > c02536 Not Changed
> > c02536 Not Changed
> > c02536 Not Changed
> > C02564 Not Changed
> > C03869 Removed
> > C03869 Added
> > C03869 Added
> >
> > What I need to accomplish is to eliminate all but one row where the number
> > and status are the same but keep ALL the rows where the number has several
> > different statuses. So basically I need the above to look like:
> >
> > Number Status
> > C02504 Removed
> > c02536 Removed
> > c02536 Added
> > c02536 Added
> > c02536 Not Changed
> > C02564 Not Changed
> > C03869 Removed
> > C03869 Added
> > C03869 Added
> >
>
>
>

[Bob Phillips]

Your results look wrong to me.

Number Status
C02504 Removed
c02536 Removed
c02536 Added
c02536 Added <<<<<<<<<<<<<<<<
c02536 Not Changed
C02564 Not Changed
C03869 Removed
C03869 Added
C03869 Added <<<<<<<<<<<<<<<<

as far as I can see the two chevron marked items are duplicates, so my code
deletes them.

Tell me in what way they are unique because I can't see it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lynn Bales" <[email protected]> wrote in message
news:[email protected]...
> Bob
>
> This works part of the way. It deletes to much. I don't want it to delete
> ANY lines for a number that has more than one status. I only need it to
> delete extra rows when the number and the status match in every case.
>
> So for a number that has 3 Adds and nothing else, delete the two extras.
For
> a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete ANY
rows.
> There is an extra column of data related to these two items I need to
> preserve but don't want to include because it creates a unique entry for
> EVERY row.
>
> I've tried filtering and it also doesn't work correctly either.
>
> Thanks so much!
>
> Lynn
>
> "Bob Phillips" wrote:
>
> > Sub Test()
> > Dim iLastRow As Long
> > Dim i As Long
> > Dim delRange As Range
> >
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For i = 2 To iLastRow
> > If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
> > Cells(i, "B").Value = Cells(i - 1, "B").Value Then
> > If delRange Is Nothing Then
> > Set delRange = Cells(i, "A")
> > Else
> > Set delRange = Union(delRange, Cells(i, "A"))
> > End If
> > End If
> > Next i
> >
> > If Not delRange Is Nothing Then
> > delRange.EntireRow.Delete
> > Set delRange = Nothing
> > End If
> >
> > End Sub
> >
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Lynn Bales" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have 4000 rows of data that looks like this:
> > >
> > > Number Status
> > > C02504 Removed
> > > C02504 Removed
> > > C02504 Removed
> > > C02504 Removed
> > > C02504 Removed
> > > C02504 Removed
> > > c02536 Removed
> > > c02536 Added
> > > c02536 Added
> > > c02536 Not Changed
> > > c02536 Not Changed
> > > c02536 Not Changed
> > > c02536 Not Changed
> > > c02536 Not Changed
> > > C02564 Not Changed
> > > C03869 Removed
> > > C03869 Added
> > > C03869 Added
> > >
> > > What I need to accomplish is to eliminate all but one row where the
number
> > > and status are the same but keep ALL the rows where the number has
several
> > > different statuses. So basically I need the above to look like:
> > >
> > > Number Status
> > > C02504 Removed
> > > c02536 Removed
> > > c02536 Added
> > > c02536 Added
> > > c02536 Not Changed
> > > C02564 Not Changed
> > > C03869 Removed
> > > C03869 Added
> > > C03869 Added
> > >
> >
> >
> >

[Lynn Bales]

Yes it works perfectly to delete any extras of the same number and status.
However, if a number shows more than one status I need to keep them all, I
don't want to delete any of them. I only want to delete extras if the status
is the same for each instances of the number.

This is the scope of why I need this in this manner. I have a number with
many options. If that number's options is all Removed, then I can make a
blanket statement that that number's status is Removed. However, a number
could have two Adds, one Remove and one Not Changed. I need to know the
options associate with all of those.

The uniqueness I spoke of involves another column of data that would make
EVERY entry unique and that's why I kept it out. I don't need to use it
except AFTER these deletes are performed.

I'm sorry if I'm not explaining this appropriately. I'm very new to Excel
functions and macros. I never used it like this before and if the 3rd column
is useful in performing this, I'm so sorry for not including it and wasting
your time....

Here's what I have in total:

number status option
123 Removed 1
123 Removed 2
123 Removed 3
987 Added 1
987 Added 2
987 Removed 3
987 Not Change 4

I would need to keep all the 987 but only one (and I don't care which one)
of 123.


"Bob Phillips" wrote:

> Your results look wrong to me.
>
> Number Status
> C02504 Removed
> c02536 Removed
> c02536 Added
> c02536 Added <<<<<<<<<<<<<<<<
> c02536 Not Changed
> C02564 Not Changed
> C03869 Removed
> C03869 Added
> C03869 Added <<<<<<<<<<<<<<<<
>
> as far as I can see the two chevron marked items are duplicates, so my code
> deletes them.
>
> Tell me in what way they are unique because I can't see it.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lynn Bales" <[email protected]> wrote in message
> news:[email protected]...
> > Bob
> >
> > This works part of the way. It deletes to much. I don't want it to delete
> > ANY lines for a number that has more than one status. I only need it to
> > delete extra rows when the number and the status match in every case.
> >
> > So for a number that has 3 Adds and nothing else, delete the two extras.
> For
> > a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete ANY
> rows.
> > There is an extra column of data related to these two items I need to
> > preserve but don't want to include because it creates a unique entry for
> > EVERY row.
> >
> > I've tried filtering and it also doesn't work correctly either.
> >
> > Thanks so much!
> >
> > Lynn
> >
> > "Bob Phillips" wrote:
> >
> > > Sub Test()
> > > Dim iLastRow As Long
> > > Dim i As Long
> > > Dim delRange As Range
> > >
> > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > For i = 2 To iLastRow
> > > If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
> > > Cells(i, "B").Value = Cells(i - 1, "B").Value Then
> > > If delRange Is Nothing Then
> > > Set delRange = Cells(i, "A")
> > > Else
> > > Set delRange = Union(delRange, Cells(i, "A"))
> > > End If
> > > End If
> > > Next i
> > >
> > > If Not delRange Is Nothing Then
> > > delRange.EntireRow.Delete
> > > Set delRange = Nothing
> > > End If
> > >
> > > End Sub
> > >
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Lynn Bales" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have 4000 rows of data that looks like this:
> > > >
> > > > Number Status
> > > > C02504 Removed
> > > > C02504 Removed
> > > > C02504 Removed
> > > > C02504 Removed
> > > > C02504 Removed
> > > > C02504 Removed
> > > > c02536 Removed
> > > > c02536 Added
> > > > c02536 Added
> > > > c02536 Not Changed
> > > > c02536 Not Changed
> > > > c02536 Not Changed
> > > > c02536 Not Changed
> > > > c02536 Not Changed
> > > > C02564 Not Changed
> > > > C03869 Removed
> > > > C03869 Added
> > > > C03869 Added
> > > >
> > > > What I need to accomplish is to eliminate all but one row where the
> number
> > > > and status are the same but keep ALL the rows where the number has
> several
> > > > different statuses. So basically I need the above to look like:
> > > >
> > > > Number Status
> > > > C02504 Removed
> > > > c02536 Removed
> > > > c02536 Added
> > > > c02536 Added
> > > > c02536 Not Changed
> > > > C02564 Not Changed
> > > > C03869 Removed
> > > > C03869 Added
> > > > C03869 Added
> > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

If you are holding back information, how can we possibly provide accurate
answers. The code cannot possibly take account of a column it knows nothing
about.

The code works perfectly within the limitations of the information that you
have provided.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lynn Bales" <[email protected]> wrote in message
news:[email protected]...
> Yes it works perfectly to delete any extras of the same number and status.
> However, if a number shows more than one status I need to keep them all, I
> don't want to delete any of them. I only want to delete extras if the
status
> is the same for each instances of the number.
>
> This is the scope of why I need this in this manner. I have a number with
> many options. If that number's options is all Removed, then I can make a
> blanket statement that that number's status is Removed. However, a number
> could have two Adds, one Remove and one Not Changed. I need to know the
> options associate with all of those.
>
> The uniqueness I spoke of involves another column of data that would make
> EVERY entry unique and that's why I kept it out. I don't need to use it
> except AFTER these deletes are performed.
>
> I'm sorry if I'm not explaining this appropriately. I'm very new to Excel
> functions and macros. I never used it like this before and if the 3rd
column
> is useful in performing this, I'm so sorry for not including it and
wasting
> your time....
>
> Here's what I have in total:
>
> number status option
> 123 Removed 1
> 123 Removed 2
> 123 Removed 3
> 987 Added 1
> 987 Added 2
> 987 Removed 3
> 987 Not Change 4
>
> I would need to keep all the 987 but only one (and I don't care which one)
> of 123.
>
>
> "Bob Phillips" wrote:
>
> > Your results look wrong to me.
> >
> > Number Status
> > C02504 Removed
> > c02536 Removed
> > c02536 Added
> > c02536 Added <<<<<<<<<<<<<<<<
> > c02536 Not Changed
> > C02564 Not Changed
> > C03869 Removed
> > C03869 Added
> > C03869 Added <<<<<<<<<<<<<<<<
> >
> > as far as I can see the two chevron marked items are duplicates, so my
code
> > deletes them.
> >
> > Tell me in what way they are unique because I can't see it.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Lynn Bales" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob
> > >
> > > This works part of the way. It deletes to much. I don't want it to
delete
> > > ANY lines for a number that has more than one status. I only need it
to
> > > delete extra rows when the number and the status match in every case.
> > >
> > > So for a number that has 3 Adds and nothing else, delete the two
extras.
> > For
> > > a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete
ANY
> > rows.
> > > There is an extra column of data related to these two items I need to
> > > preserve but don't want to include because it creates a unique entry
for
> > > EVERY row.
> > >
> > > I've tried filtering and it also doesn't work correctly either.
> > >
> > > Thanks so much!
> > >
> > > Lynn
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Sub Test()
> > > > Dim iLastRow As Long
> > > > Dim i As Long
> > > > Dim delRange As Range
> > > >
> > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > > For i = 2 To iLastRow
> > > > If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
> > > > Cells(i, "B").Value = Cells(i - 1, "B").Value Then
> > > > If delRange Is Nothing Then
> > > > Set delRange = Cells(i, "A")
> > > > Else
> > > > Set delRange = Union(delRange, Cells(i, "A"))
> > > > End If
> > > > End If
> > > > Next i
> > > >
> > > > If Not delRange Is Nothing Then
> > > > delRange.EntireRow.Delete
> > > > Set delRange = Nothing
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Lynn Bales" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have 4000 rows of data that looks like this:
> > > > >
> > > > > Number Status
> > > > > C02504 Removed
> > > > > C02504 Removed
> > > > > C02504 Removed
> > > > > C02504 Removed
> > > > > C02504 Removed
> > > > > C02504 Removed
> > > > > c02536 Removed
> > > > > c02536 Added
> > > > > c02536 Added
> > > > > c02536 Not Changed
> > > > > c02536 Not Changed
> > > > > c02536 Not Changed
> > > > > c02536 Not Changed
> > > > > c02536 Not Changed
> > > > > C02564 Not Changed
> > > > > C03869 Removed
> > > > > C03869 Added
> > > > > C03869 Added
> > > > >
> > > > > What I need to accomplish is to eliminate all but one row where
the
> > number
> > > > > and status are the same but keep ALL the rows where the number has
> > several
> > > > > different statuses. So basically I need the above to look like:
> > > > >
> > > > > Number Status
> > > > > C02504 Removed
> > > > > c02536 Removed
> > > > > c02536 Added
> > > > > c02536 Added
> > > > > c02536 Not Changed
> > > > > C02564 Not Changed
> > > > > C03869 Removed
> > > > > C03869 Added
> > > > > C03869 Added
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Lynn Bales]

I understand your frustration with me and I apologize again for wasting your
time. It certainly was not my intent to mislead you with inaccurate
information. I truly didn't think it was necessary because I am just now
learning how these things work.

Thank you for all the information you have provide and I'll see if I can
work thru the real issue with it.

"Bob Phillips" wrote:

> If you are holding back information, how can we possibly provide accurate
> answers. The code cannot possibly take account of a column it knows nothing
> about.
>
> The code works perfectly within the limitations of the information that you
> have provided.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lynn Bales" <[email protected]> wrote in message
> news:[email protected]...
> > Yes it works perfectly to delete any extras of the same number and status.
> > However, if a number shows more than one status I need to keep them all, I
> > don't want to delete any of them. I only want to delete extras if the
> status
> > is the same for each instances of the number.
> >
> > This is the scope of why I need this in this manner. I have a number with
> > many options. If that number's options is all Removed, then I can make a
> > blanket statement that that number's status is Removed. However, a number
> > could have two Adds, one Remove and one Not Changed. I need to know the
> > options associate with all of those.
> >
> > The uniqueness I spoke of involves another column of data that would make
> > EVERY entry unique and that's why I kept it out. I don't need to use it
> > except AFTER these deletes are performed.
> >
> > I'm sorry if I'm not explaining this appropriately. I'm very new to Excel
> > functions and macros. I never used it like this before and if the 3rd
> column
> > is useful in performing this, I'm so sorry for not including it and
> wasting
> > your time....
> >
> > Here's what I have in total:
> >
> > number status option
> > 123 Removed 1
> > 123 Removed 2
> > 123 Removed 3
> > 987 Added 1
> > 987 Added 2
> > 987 Removed 3
> > 987 Not Change 4
> >
> > I would need to keep all the 987 but only one (and I don't care which one)
> > of 123.
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Your results look wrong to me.
> > >
> > > Number Status
> > > C02504 Removed
> > > c02536 Removed
> > > c02536 Added
> > > c02536 Added <<<<<<<<<<<<<<<<
> > > c02536 Not Changed
> > > C02564 Not Changed
> > > C03869 Removed
> > > C03869 Added
> > > C03869 Added <<<<<<<<<<<<<<<<
> > >
> > > as far as I can see the two chevron marked items are duplicates, so my
> code
> > > deletes them.
> > >
> > > Tell me in what way they are unique because I can't see it.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Lynn Bales" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Bob
> > > >
> > > > This works part of the way. It deletes to much. I don't want it to
> delete
> > > > ANY lines for a number that has more than one status. I only need it
> to
> > > > delete extra rows when the number and the status match in every case.
> > > >
> > > > So for a number that has 3 Adds and nothing else, delete the two
> extras.
> > > For
> > > > a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete
> ANY
> > > rows.
> > > > There is an extra column of data related to these two items I need to
> > > > preserve but don't want to include because it creates a unique entry
> for
> > > > EVERY row.
> > > >
> > > > I've tried filtering and it also doesn't work correctly either.
> > > >
> > > > Thanks so much!
> > > >
> > > > Lynn
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Sub Test()
> > > > > Dim iLastRow As Long
> > > > > Dim i As Long
> > > > > Dim delRange As Range
> > > > >
> > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > > > For i = 2 To iLastRow
> > > > > If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
> > > > > Cells(i, "B").Value = Cells(i - 1, "B").Value Then
> > > > > If delRange Is Nothing Then
> > > > > Set delRange = Cells(i, "A")
> > > > > Else
> > > > > Set delRange = Union(delRange, Cells(i, "A"))
> > > > > End If
> > > > > End If
> > > > > Next i
> > > > >
> > > > > If Not delRange Is Nothing Then
> > > > > delRange.EntireRow.Delete
> > > > > Set delRange = Nothing
> > > > > End If
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Lynn Bales" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I have 4000 rows of data that looks like this:
> > > > > >
> > > > > > Number Status
> > > > > > C02504 Removed
> > > > > > C02504 Removed
> > > > > > C02504 Removed
> > > > > > C02504 Removed
> > > > > > C02504 Removed
> > > > > > C02504 Removed
> > > > > > c02536 Removed
> > > > > > c02536 Added
> > > > > > c02536 Added
> > > > > > c02536 Not Changed
> > > > > > c02536 Not Changed
> > > > > > c02536 Not Changed
> > > > > > c02536 Not Changed
> > > > > > c02536 Not Changed
> > > > > > C02564 Not Changed
> > > > > > C03869 Removed
> > > > > > C03869 Added
> > > > > > C03869 Added
> > > > > >
> > > > > > What I need to accomplish is to eliminate all but one row where
> the
> > > number
> > > > > > and status are the same but keep ALL the rows where the number has
> > > several
> > > > > > different statuses. So basically I need the above to look like:
> > > > > >
> > > > > > Number Status
> > > > > > C02504 Removed
> > > > > > c02536 Removed
> > > > > > c02536 Added
> > > > > > c02536 Added
> > > > > > c02536 Not Changed
> > > > > > C02564 Not Changed
> > > > > > C03869 Removed
> > > > > > C03869 Added
> > > > > > C03869 Added
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[STEVE BELL]

Bob,

I greatly respect your inputs to this forum. You are continually very
helpful.

Your sentiments are well meant:
It is important that the OP provide as much information as is needed.

But your response sounds a little harsh (I don't think you meant it that
way), especially since the OP is a newby and hasn't learned the ins & outs
of coding and asking for help.

Newby's are still struggling with spelling out what they need and how to get
there...
This is an issue that comes up most times when I am building an application
for most people.
It is usually the hardest part and takes a lot of patience.

have a good!

--
steveB

Remove "AYN" from email to respond
"Bob Phillips" <[email protected]> wrote in message
news:eIqLb%[email protected]...
> If you are holding back information, how can we possibly provide accurate
> answers. The code cannot possibly take account of a column it knows
> nothing
> about.
>
> The code works perfectly within the limitations of the information that
> you
> have provided.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lynn Bales" <[email protected]> wrote in message
> news:[email protected]...
>> Yes it works perfectly to delete any extras of the same number and
>> status.
>> However, if a number shows more than one status I need to keep them all,
>> I
>> don't want to delete any of them. I only want to delete extras if the
> status
>> is the same for each instances of the number.
>>
>> This is the scope of why I need this in this manner. I have a number with
>> many options. If that number's options is all Removed, then I can make a
>> blanket statement that that number's status is Removed. However, a number
>> could have two Adds, one Remove and one Not Changed. I need to know the
>> options associate with all of those.
>>
>> The uniqueness I spoke of involves another column of data that would make
>> EVERY entry unique and that's why I kept it out. I don't need to use it
>> except AFTER these deletes are performed.
>>
>> I'm sorry if I'm not explaining this appropriately. I'm very new to Excel
>> functions and macros. I never used it like this before and if the 3rd
> column
>> is useful in performing this, I'm so sorry for not including it and
> wasting
>> your time....
>>
>> Here's what I have in total:
>>
>> number status option
>> 123 Removed 1
>> 123 Removed 2
>> 123 Removed 3
>> 987 Added 1
>> 987 Added 2
>> 987 Removed 3
>> 987 Not Change 4
>>
>> I would need to keep all the 987 but only one (and I don't care which
>> one)
>> of 123.
>>
>>
>> "Bob Phillips" wrote:
>>
>> > Your results look wrong to me.
>> >
>> > Number Status
>> > C02504 Removed
>> > c02536 Removed
>> > c02536 Added
>> > c02536 Added <<<<<<<<<<<<<<<<
>> > c02536 Not Changed
>> > C02564 Not Changed
>> > C03869 Removed
>> > C03869 Added
>> > C03869 Added <<<<<<<<<<<<<<<<
>> >
>> > as far as I can see the two chevron marked items are duplicates, so my
> code
>> > deletes them.
>> >
>> > Tell me in what way they are unique because I can't see it.
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Lynn Bales" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > Bob
>> > >
>> > > This works part of the way. It deletes to much. I don't want it to
> delete
>> > > ANY lines for a number that has more than one status. I only need it
> to
>> > > delete extra rows when the number and the status match in every case.
>> > >
>> > > So for a number that has 3 Adds and nothing else, delete the two
> extras.
>> > For
>> > > a number that has 2 Adds, 1 Removed and 1 Not Changed, don't delete
> ANY
>> > rows.
>> > > There is an extra column of data related to these two items I need to
>> > > preserve but don't want to include because it creates a unique entry
> for
>> > > EVERY row.
>> > >
>> > > I've tried filtering and it also doesn't work correctly either.
>> > >
>> > > Thanks so much!
>> > >
>> > > Lynn
>> > >
>> > > "Bob Phillips" wrote:
>> > >
>> > > > Sub Test()
>> > > > Dim iLastRow As Long
>> > > > Dim i As Long
>> > > > Dim delRange As Range
>> > > >
>> > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
>> > > > For i = 2 To iLastRow
>> > > > If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
>> > > > Cells(i, "B").Value = Cells(i - 1, "B").Value Then
>> > > > If delRange Is Nothing Then
>> > > > Set delRange = Cells(i, "A")
>> > > > Else
>> > > > Set delRange = Union(delRange, Cells(i, "A"))
>> > > > End If
>> > > > End If
>> > > > Next i
>> > > >
>> > > > If Not delRange Is Nothing Then
>> > > > delRange.EntireRow.Delete
>> > > > Set delRange = Nothing
>> > > > End If
>> > > >
>> > > > End Sub
>> > > >
>> > > >
>> > > >
>> > > > --
>> > > >
>> > > > HTH
>> > > >
>> > > > RP
>> > > > (remove nothere from the email address if mailing direct)
>> > > >
>> > > >
>> > > > "Lynn Bales" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > > I have 4000 rows of data that looks like this:
>> > > > >
>> > > > > Number Status
>> > > > > C02504 Removed
>> > > > > C02504 Removed
>> > > > > C02504 Removed
>> > > > > C02504 Removed
>> > > > > C02504 Removed
>> > > > > C02504 Removed
>> > > > > c02536 Removed
>> > > > > c02536 Added
>> > > > > c02536 Added
>> > > > > c02536 Not Changed
>> > > > > c02536 Not Changed
>> > > > > c02536 Not Changed
>> > > > > c02536 Not Changed
>> > > > > c02536 Not Changed
>> > > > > C02564 Not Changed
>> > > > > C03869 Removed
>> > > > > C03869 Added
>> > > > > C03869 Added
>> > > > >
>> > > > > What I need to accomplish is to eliminate all but one row where
> the
>> > number
>> > > > > and status are the same but keep ALL the rows where the number
>> > > > > has
>> > several
>> > > > > different statuses. So basically I need the above to look like:
>> > > > >
>> > > > > Number Status
>> > > > > C02504 Removed
>> > > > > c02536 Removed
>> > > > > c02536 Added
>> > > > > c02536 Added
>> > > > > c02536 Not Changed
>> > > > > C02564 Not Changed
>> > > > > C03869 Removed
>> > > > > C03869 Added
>> > > > > C03869 Added
>> > > > >
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >
>
>

[Bob Phillips]

It is not frustration Lynn, it is just that if I don't have all the
information, I cannot supply an accurate answer. I pointed out that it
seemed fine with the info provided, and you responded by telling me there
was another item, but didn't tell me what. I can't second-guess it.

Bob


"Lynn Bales" <[email protected]> wrote in message
news:[email protected]...
> I understand your frustration with me and I apologize again for wasting
your
> time. It certainly was not my intent to mislead you with inaccurate
> information. I truly didn't think it was necessary because I am just now
> learning how these things work.
>
> Thank you for all the information you have provide and I'll see if I can
> work thru the real issue with it.
>
> "Bob Phillips" wrote:
>
> > If you are holding back information, how can we possibly provide
accurate
> > answers. The code cannot possibly take account of a column it knows
nothing
> > about.
> >
> > The code works perfectly within the limitations of the information that
you
> > have provided.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)