Quick help requested!! "Grouping" Formula or VBA fix

[mocp2008]

Hi Everyone, haven't been here in a while!

I'm desperately (and quickly!) needing a way to do the following. A formula is preferred, but a VBA method might work ok, too.

(Simplifying a bit here)..

In a column, let's say colC, I might have several "start" and "stop" points - these are placed to correspond with certain data in colD.

I'm looking for something that can identify all sets data BETWEEN this "Start" and "Stop" points in colC. So, looking for something which labels all that data as a particular word; "true", "data", "information" or something like that.

This formula will be in colB. Needs to begin identifying "grouping" when it catches a "Start" point in colC, and end grouping when it hits a "Stop" point in colC. It then leaves cells blank until it hits the NEXT "Start" point, then groups until the next "Stop" point, etc.

I've attached a bit of an example spreadsheet. Hopefully this makes sense! Thanks everyone!!

[deadlyduck]

Does the attached help? The yellow column contains the relevant formula.

PS- You may need to refer to the forum rules re descriptive titles to your posts. I suspect a moderator may view your 'Quick help requested' title as stretching the envelope a bit too much.....

[mocp2008]

Thanks. That worked, except that it did not include the "Stop" point in the group. It's ending one line before it.

But, it looks like a formula fix for this isn't going to work... It took an hr to run what looks to be about 20% (it's a VERY long file.) Any suggestions for a VBA solution?

(I fixed the header to be a bit more descriptive, too. Thanks)

[deadlyduck]

Hi,

I'm not a VBA person so am afraid I can't help you there.

Could you explain how my formula doesn't include the 'Stop' point/ is ending one line before it. As I see it, the word 'information' IS being entered in all cells between 'Start' and 'Stop' and includes the cells immediately adjacent to 'Start' and 'Stop'. If it's supposed to enter the word 'Information' 1 line after the word 'Stop' the proposed formula could be changed.

However, as you point out, the VBA approach may be a better approach so I won't propose a change yet.

[StephenR]

deadlyduck's formula seems to work and I thought it was rather clever. Here is a VBA approach:

Sub x()
 
Dim rStart As Range, rStop As Range, sAddress As String

Application.ScreenUpdating = False

With Sheet1.Range("C1", Range("C" & Rows.Count).End(xlUp))
    Set rStart = .Cells(.Rows.Count, 1)
    Set rStop = .Cells(.Rows.Count, 1)
    Set rStart = .Find(What:="Start", After:=rStop, LookIn:=xlFormulas, _
                       LookAt:=xlWhole, SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rStart Is Nothing Then
        sAddress = rStart.Address
        Do
            Set rStop = .Find(What:="Stop", After:=rStart, LookIn:=xlFormulas, _
                              LookAt:=xlWhole, SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            Range(rStart, rStop).Offset(, -1) = "Information"
            Set rStart = .Find(What:="Start", After:=rStop, LookIn:=xlFormulas, _
                               LookAt:=xlWhole, SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        Loop While rStart.Address <> sAddress
    End If
End With

Application.ScreenUpdating = True

End Sub

[martindwilson]

you could try in E2 (but you must have a blank above e2 to start)
=IF(OR(C2="start",ISTEXT(E1)),(IF(AND(C2="",E1=""),"",IF(C1="stop","",B2))),"")