Search down a sorted list for a break in the pattern

SteveD

Board Regular
Joined
Feb 20, 2002
Messages
104
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
If A1:A4 contains...

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

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

=A2<>A1
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top