I have a single excel sheet with data about hundreds of cities, for example:

etc. and so on

I am trying to think of a way that I could Mark all the rows belonging to specific States as TRUE. For example, if my desired states were just NY and FL, the above examples from the sheet would look like this.

So essentially I need to set something up that will read the first cell and if it detects a state from my list it marks TRUE for that row as well as the three rows that follow immediately after. I tried using COUNTIF and also ISNUMBER and SEARCH where I basically have it always checking the three above rows for the desired strings that way the whole section for a city will be selected as TRUE but the rows immediately below will be out of reach and therefore FALSE, but that method doesn't work.

Eventually, I will use the TRUE to delete the hundreds of non TRUE rows so i'm only left with the info for the desired cities. How do I accomplish this (through formulas, macros, or otherwise) any help is greatly appreciated.

CodePudding user response：

Flag Groups of Data

Sub GenerateCriteriaColumn()
    
    Const sFirstCellAddress As String = "A2"
    Const dFirstCellAddress As String = "D2"
    Const RepeatCount As Long = 4
    Const CriteriaList As String = "NY,FL"
    Const YesCriteria As Variant = True
    Const NoCriteria As Variant = False
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim srg As Range
    Dim rCount As Long
    
    With ws.Range(sFirstCellAddress)
        Dim lCell As Range
        Set lCell = .Resize(ws.Rows.Count - .Row   1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column range
        rCount = lCell.Row - .Row   1
        Set srg = .Resize(rCount)
    End With
    
    Dim Data As Variant: Data = srg.Value
    
    Dim Criteria() As String: Criteria = Split(CriteriaList, ",")
    Dim cUpper As Long: cUpper = UBound(Criteria)
    
    Dim r As Long
    Dim r1 As Long
    Dim c As Long
    
    For r = 1 To rCount
        For c = 0 To cUpper
            If Right(Trim(Data(r, 1)), 2) = Criteria(c) Then Exit For
        Next c
        If c > cUpper Then ' no match found
            Data(r, 1) = NoCriteria
        Else ' match found
            For r1 = r To r   RepeatCount - 1
                Data(r1, 1) = YesCriteria
            Next r1
            r = r1 - 1
        End If
    Next r
    
    With ws.Range(dFirstCellAddress)
        .Resize(rCount).Value = Data
        .Resize(ws.Rows.Count - .Row - rCount   1).Offset(rCount).Clear
    End With
    
    MsgBox "Criteria column generated.", vbInformation
    
End Sub

CodePudding user response：

If you think a formula is acceptable and if you have Office 365, you can do:

=SCAN( 0,
       BYROW( ISNUMBER(FIND( ", "& D1:E1,A2:A17 ) ), LAMBDA(a, OR(a)  ) )*5,
       LAMBDA(a,b,MIN(a-b 1,0))) < 0

where D1:E1 is a range that contains the state codes you want to identify and A2:A17 is your list of city names.

CodePudding user response：

I'd definitely go with one of the other answers, but just to give a third option.

This formula is long, but once you set up the first cell you can ctrl d to fill in the rest.

=IF(ISNUMBER(MATCH(RIGHT(A2,2),$G$2:$G$6,0)),"TRUE",IF(A2="pop.",IF(ISNUMBER(MATCH(RIGHT(OFFSET(A2,-1,0),2),$G$2:$G$6,0)),"TRUE","FALSE"),IF(A2="size",IF(ISNUMBER(MATCH(RIGHT(OFFSET(A2,-2,0),2),$G$2:$G$6,0)),"TRUE","FALSE"),IF(A2="gdp",IF(ISNUMBER(MATCH(RIGHT(OFFSET(A2,-3,0),2),$G$2:$G$6,0)),"TRUE","FALSE"),"FALSE"))))

Where G2:G6 is the range of states you want to include.

This formula uses IF statements to check if the last 2 letters of a cell (the state) are in the range specified. Should the cell value be pop., size, or GDP, then it offsets to return to the cell with the city and state name.