Search down a sorted list for a break in the pattern

[SteveD]

I am looking for a way to select the first row where a break in a pattern in the column is found. For example:
Row Cell Value
1 Keep
2 Keep
3 Keep
4 No

I want to select row 4 because it has a different value than the cell above it (in row 3).

Thank you with your help on this,
SteveD

 

[Mark W.]

If A1:A4 contains...

{"Field1"
;"Keep"
;"Keep"
;"Keep"
;"No"}

...you could use an Advanced Autofilter referencing a computed Criteria using the formula...

=A2<>A1

 

[Aladin Akyurek]

On 2002-04-18 13:29, SteveD wrote:
I am looking for a way to select the first row where a break in a pattern in the column is found. For example:
Row Cell Value
1 Keep
2 Keep
3 Keep
4 No

I want to select row 4 because it has a different value than the cell above it (in row 3).

Thank you with your help on this,
SteveD

This reports the row number of the first row where a change in pattern occurs:

=MATCH(A2,$A$2:$A$5)+1

 

[SteveD]

Thanks Mark,
The spread sheet is 13,000 rows and I never know where the break off is. I'm writing a macro that will delete all of the rows that have the "no" value and below. Is there an easy way to put this in?

Thanks,
SteveD
This message was edited by SteveD on 2002-04-18 14:23

 

[Mark W.]

On 2002-04-18 14:22, SteveD wrote:
Thanks Mark,
The spread sheet is 13,000 rows and I never know where the break off is. I'm writing a macro that will delete all of the rows that have the "no" value and below. Is there an easy way to put this in?

Thanks,
SteveD
This message was edited by SteveD on 2002-04-18 14:23

The AutoFilter finds where 'Value' changes regardless of where. Using the Criteria, =A2<>A1, Excel doesn't just examine these 2 cells. As it traverses the data list these relative cell references are incremented like a loop counter. When you say, "...delete all of the rows that have the "no" value and below", do you intend to delete "Keep" records subsequent to the 1st occurrence of "No"?

 

[SteveD]

Ahhh. Ok. I got it.
Thank you for your help!
SteveD

 

[Yogi Anand]

On 2002-04-18 13:37, Mark W. wrote:
If A1:A4 contains...

{"Field1"
;"Keep"
;"Keep"
;"Keep"
;"No"}

...you could use an Advanced Autofilter referencing a computed Criteria using the formula...

=A2<>A1

Hi Mark:
I see the logic of using the computed criteria ... I have used it during my Lotus 123 days, but I can't make it work with Excel. I will appreciate your looking at it and providing your comments.

Regards!

Yogi Anand
This message was edited by Yogi Anand on 2002-04-19 09:01

 

[Mark W.]

On 2002-04-19 09:00, Yogi Anand wrote:

On 2002-04-18 13:37, Mark W. wrote:
If A1:A4 contains...

{"Field1"
;"Keep"
;"Keep"
;"Keep"
;"No"}

...you could use an Advanced Autofilter referencing a computed Criteria using the formula...

=A2<>A1

Hi Mark:
I see the logic of using the computed criteria ... I have used it during my Lotus 123 days, but I can't make it work with Excel. I will appreciate your looking at it and providing your comments.

Regards!

Yogi Anand
This message was edited by Yogi Anand on 2002-04-19 09:01

Yogi, a common mistake made with computed criteria is to enter the field name in the 1st row of the Criteria range. The 1st row should remain blank. See the Excel Help Index topic for "Examples of advanced filter criteria". Especially, read the "Conditions created as the result of a formula" paragraph.

 

[Yogi Anand]

T H A N K S M A R K !
That was it -- I have not read the Help topic yet, but once I took the Field name out it worked just like you said it would.

Regards!

Yogi Anand

 

[klb]

Here is some code that I use to do just the opposite, insert a blank row when it finds a break in the pattern. Don't see why it can't be modified to delete the row instead of inserting one, but I may be wrong.

Option Explicit
Dim strBottomOfList As String
Dim strRecordNumber As String
Sub InsertBlankRows()
Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Range("A1").Select

Do While ActiveCell.Value <> ""

strRecordNumber = ActiveCell.Value

Do Until ActiveCell.Value <> strRecordNumber

ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.EntireRow.Insert

ActiveCell.Offset(1, 0).Select

Loop

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True
End Sub