Need help with an Excel formula

[aphex]

Trying to see if this is possible, any help would be appreciated.

I need a formula to look at a row and count if any of the cell's in that row contain part of a list of words defined in a named range.

Simplified Example/ - I would need A2, A3, A4 and A5 to count the number of times (or even just a YES/NO) that the row contains a value in the Named Range defined below. I also need it to count with wildcards, such as where 'Lima, Peru' in C5 is defined, i would need those to count as well as 'Peru' is in the Named Range.

I tried multiple countif functions, but as I my defined range is nearly 300 items, its becoming too unwieldy. Any ideas?

 

[Binky]

You could use multiple countif functions on another tab or outside of the print range (one for each cell in named range), then sum them up anywhere you need to display the total.

 

[mpo]

You can try creating an array--

=SUMPRODUCT(LEN($B$2:$E$5)- LEN(SUBSTITUTE($B$2:$E$5,A8,"")))/LEN(A8)

 

[aphex]

You can try creating an array--

=SUMPRODUCT(LEN($B$2:$E$5)- LEN(SUBSTITUTE($B$2:$E$5,A8,"")))/LEN(A8)

Hmmmm, interesting. That seems to look at the entire batch of data from B2 to E5 at one time and search for Peru, is there a way to make it look row by row for any of the available options in the Named Range?

 

[postmark]

How big is the actual table for this? There is a way to do it, but it might be painful if you have a huge array and or named range.

 

[aphex]

How big is the actual table for this? There is a way to do it, but it might be painful if you have a huge array and or named range.

Huge. About 6000 rows. Named range is 250-300.

 

[postmark]

This should do it for you. Just need to setup the variables to your table dimensions and you should be good to go. Let me know if you need any other help on it.

Sub Macro10()
'
' Macro10 Macro
'

'
    Dim toprow As Integer
    Dim bottomrow As Integer
    Dim firstcolumn As Integer
    Dim lastcolumn As Integer
    Dim namedrange As String
    Dim c As Range
    Dim i As Integer
    Dim count As Integer
    Dim findwhat As String
    Dim comparewhat As String
    
    'change these to match your table params
    toprow = 2
    bottomrow = 5
    firstcolumn = 2
    lastcolumn = 5
    namedrange = "name"
    
    For i = toprow To bottomrow
    
        For Each Cell In Range(Cells(i, firstcolumn), Cells(i, lastcolumn))
    
            comparewhat = UCase(Cell.Value)
            
            For Each c In Range(namedrange)
                
                findwhat = UCase(c.Value)

                If InStr(1, comparewhat, findwhat, 1) <> 0 Then
                    count = count + 1
                End If
            
            Next c
    
        Next Cell

    Cells(i, 1).Select
    Cells(i, 1) = count
    count = 0
    
    
    Next i
End Sub

 

[aphex]

This should do it for you. Just need to setup the variables to your table dimensions and you should be good to go. Let me know if you need any other help on it.

Sub Macro10()
'
' Macro10 Macro
'

'
    Dim toprow As Integer
    Dim bottomrow As Integer
    Dim firstcolumn As Integer
    Dim lastcolumn As Integer
    Dim namedrange As String
    Dim c As Range
    Dim i As Integer
    Dim count As Integer
    Dim findwhat As String
    Dim comparewhat As String
    
    'change these to match your table params
    toprow = 2
    bottomrow = 5
    firstcolumn = 2
    lastcolumn = 5
    namedrange = "name"
    
    For i = toprow To bottomrow
    
        For Each Cell In Range(Cells(i, firstcolumn), Cells(i, lastcolumn))
    
            comparewhat = UCase(Cell.Value)
            
            For Each c In Range(namedrange)
                
                findwhat = UCase(c.Value)

                If InStr(1, comparewhat, findwhat, 1) <> 0 Then
                    count = count + 1
                End If
            
            Next c
    
        Next Cell

    Cells(i, 1).Select
    Cells(i, 1) = count
    count = 0
    
    
    Next i
End Sub

Works PERFECT!!! Thanks a ton!

 

[postmark]

You bet. Anytime.