Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Search down a sorted list for a break in the pattern

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If A1:A4 contains...

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

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

    =A2<>A1

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,630
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"?

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •