Finding and Counting for text within a string in a column

Hi,

I'm trying to teach myself excel and apply what I learn at work. Here's my problem:

AF26AYN25
AF25BYN21
AU34CNYY22
CE221AYYY10
EU13BYN2
GR83DYN1
MI14BNYY15
NO53BYN6
PA66BYN25
SO51CYN5
AS44DNYN8

These serial numbers represent buildings in various places around the world.

I'm trying to count particular types of buildings, such as, all the buildings in Asia (AS in the first two digits).

Creating the serial numbers was my "bright" idea for simplifying finding and counting from a table with lots of conditional logic (CountIf Column A="Asia", and Column B="B", and Column C="Yes", and Column D="No"...). I could not get a countif formula to work with and: AND(COUNTIF( , ), COUNTIF( , ). If comes back true, but doesn't count the cells I asked it to.

So, I build this code system, which may be much better in the long run, or I may have wasted all afternoon now that I realize I don't know how to search within the string. I tried simply using CountIf with all wilcards except for the text in the digits I'm interested it: (AS????5????25), which doesn't work. Here's the code table:


Region Code City Code Asset Type Code Criticality Code Plan Created Code Satellite Code Hub City Hub Plan Code Risk Profile Code Asset Code

AF 2 6 A Y N 25 AF26AYN25
AF 2 5 B Y N 21 AF25BYN21
AU 3 4 C N Y 22 Y 22 AU34CNYY22
CE 22 1 A Y Y 1 Y 10 CE221AYYY10
EU 1 3 B Y N 2 EU13BYN2
GR 8 3 D Y N 1 GR83DYN1
MI 1 4 B N Y 2 Y 15 MI14BNYY15
NO 5 3 B Y N 6 NO53BYN6
PA 6 6 B Y N 25 PA66BYN25
SO 5 1 C Y N 5 SO51CYN5
AS 4 4 D N Y 6 N 8 AS44DNYN8

Am I just making my life harder for myself? Should I just build lots of smaller tables and use the sum function for each specific type of building I'm looking for (dozens of tables). I honestly thought I'd be able to search for the text in the string with one function, and it would be clean and easy.

Any help?

Post a workbook with a sample table and indicate what you counted and why you got the number you got.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

One way:

=COUNTIF(A:A,"AF*")


Regards, TMS

Hi and welcome to the forum :0

Im a little unsure on what your data looks like (columns/rows etc). Could you upload a sample worlbook, showing what you have (no sensitive info), what your expected outcome would be, and how you arrived at that?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

For Starters (though I'm not sure about all those spaces):

Option Explicit
Private Sub ExecuteSearch_Click()
    Dim CompareBasis    As String, _
        CodeList        As Range, _
        CodeItem        As Range, _
        FoundCount      As Long
        
    Set CodeList = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    CompareBasis = UCase(SearchParam.Text)
    
    Range("D2").Value = ""
    For Each CodeItem In CodeList
        If CodeItem Like CompareBasis Then
            FoundCount = FoundCount + 1
        End If
    Next CodeItem
    Range("D2").Value = FoundCount
End Sub

Originally Posted by WanderingDog

Hi,
Creating the serial numbers was my "bright" idea for simplifying finding and counting from a table with lots of conditional logic (CountIf Column A="Asia", and Column B="B", and Column C="Yes", and Column D="No"...). I could not get a countif formula to work with and: AND(COUNTIF( , ), COUNTIF( , ). If comes back true, but doesn't count the cells I asked it to.

Is it a "bright" idea to do the combination while COUNTIFS or SUMPRODUCT can do this?
With table like this:
A B C D E F G
AF 2 6 A Y N 25
AF 2 5 B Y N 21
AU 3 4 C N Y Y 22

Count A column with "AF" and C column equals 6:
=COUNTIFS(A2:A4,"AF",C2:C4,6)
=SUMPRODUCT((A2:A4="AF")*(C2:C4=6))
=1
Does it work?

Guys, if you look at te provided workbook, there are sample answers in there that, so far, would not work with any of the suggestions

Originally Posted by FDibbins

Guys, if you look at te provided workbook, there are sample answers in there that, so far, would not work with any of the suggestions

The OP said in #1 that he couldnot use COUNTIF for multi columns from original table, so he have to create combination code to search

Private Sub ExecuteSearch_Click()
    Range("D2").Value = ""
    Dim CompareBasis    As String, _
        CodeList        As Range, _
        AssetCode       As Range, _
        FoundCount      As Long, _
        CompareTail     As Long, _
        AssetTail       As Long, _
        LessThan        As Long, _
        GreaterThan     As Long

    Set CodeList = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    CompareBasis = UCase(SearchParam.Text)
    
    'check for "<>" in comparison string
    LessThan = InStr(SearchParam.Text, "<")
    GreaterThan = InStr(SearchParam.Text, ">")
    
    If LessThan Or GreaterThan Then
        'strip off the last characters starting with "<  & >"
        CompareBasis = Left(CompareBasis, WorksheetFunction.Max(LessThan, GreaterThan) - 1)
        
        ' get number at end of compare string
        CompareTail = CLng(Mid(SearchParam.Text, WorksheetFunction.Max(LessThan, GreaterThan) + 1))
    End If
    
    For Each AssetCode In CodeList
        
        If AssetCode Like CompareBasis Then
        
            If LessThan Or GreaterThan Then
            
                'get number at end of asset code
                AssetTail = CLng(Mid(AssetCode.Value, InStrRev(AssetCode.Value, " ") + 1))
                
                If LessThan And AssetTail < CompareTail Then FoundCount = FoundCount + 1
                If GreaterThan And AssetTail > CompareTail Then FoundCount = FoundCount + 1
            Else
                FoundCount = FoundCount + 1
            End If
        End If
    Next AssetCode
    Range("D2").Value = FoundCount
End Sub