Copper Contributor

May 31, 2018

Solved

Formula to search multiple tabs and return a count

hello, I am needing help on this formula. I have about 2 dozen tabs in my excel spreadsheet. Each tab is a recipe and I update each one on the production day (Julian date).

Example we made four recipes on date 150. I need a formula that would search all tabs and look for "150" and then count downward how many batches.

It would search C1:ZZ1 in each tab for an instance of "150"

Then on finding it, it would count how many batches (17 in this instance on this tab) and total them up for al tabs (Example 10 batches each on 4 tabs would be 40 total)

I would also need a formula that would count the recipes. There is 1 recipe to each tab. If "150" is on four tabs then it would be four recipes.

I have been working on this all day and I have hit a wall. Any help would be greatly appreciated!

David-

Using your TEST.xlsx file this VBA Code appears to yield the correct results (See Below Code):

End Result after VBA Code is executed:

I have commented the code for better understanding.... it may need to be modified based on the way your file is set up:

Sub GetRecipeCounts()

    Dim ProdLrow As Integer
    Dim ColLp As Integer
    Dim RecipeDay As String
    Dim RecipeCount As Integer
    
    'Define last row on Production sheet based on column A
    ProdLrow = Sheets("PRODUCTION").Cells(Rows.Count, "A").End(xlUp).Row
    
    'Loop through values on the PRODUCTION Sheet in Column A
    For intLp = 2 To ProdLrow
        
        'Define Day to look for...Note this is a strinf because some of your days have -X in them..
        RecipeDay = Sheets("PRODUCTION").Cells(intLp, "A")
        
        'Loop through Worksheets
        For Each Worksheet In ThisWorkbook.Worksheets
            
            'Skip over PRODUCTION worksheet...b/c there is no data on it
            If Worksheet.Name <> "PRODUCTION" Then
                
                '3 to 702 represents Column C to Column ZZ
                For ColLp = 3 To 702
                
                    'Look through first set of values in Row 1
                    'Note we use the left function to get the first 3 characters....
                    If Left(Worksheet.Cells(1, ColLp), 3) = RecipeDay Then
                        'Get count for RecipeDay... note 2 and 17 stand for the rows
                        'ColLp signifies the Column the RecipeDay was found....
                        'We also need to add it to the prior value in case it's the second instance....
                        RecipeCount = RecipeCount + Application.CountA(Worksheet.Range(Worksheet.Cells(2, ColLp), Worksheet.Cells(17, ColLp)))
                    End If
                    
                    'Look through the second set of values on row 28
                    If Left(Worksheet.Cells(28, ColLp), 3) = RecipeDay Then
                        'Get count for RecipeDay... note 29 and 44 stand for the rows
                        'ColLp signifies the Column the RecipeDay was found....
                        'We also need to add it to the prior value in case it's the second instance....
                        RecipeCount = RecipeCount + Application.CountA(Worksheet.Range(Worksheet.Cells(29, ColLp), Worksheet.Cells(44, ColLp)))
                    End If
                    
                Next ColLp
           
            End If
        
        Next Worksheet
        
        'Input the counts to the PRODUCTION worksheet
        Sheets("PRODUCTION").Cells(intLp, "B") = RecipeCount
        
        'Reset the RecipeCount for next Loop....i.e. next RecipeDay
        RecipeCount = 0
    
    Next intLp

End Sub

If you are unfamiliar how to use VBA Code you can use this link as a reference:

http://www.contextures.com/xlvba01.html

Hope this helps. Please let me know if you need further assistance.

5 Replies

Matt Mickle

Bronze Contributor

May 31, 2018

I may be wrong.... but I'm pretty sure what you require won't be possible without a few helper formulas or some fairly extensive VBA. Especially if you're going to need to replicate it for different numbers like : 142, 143, 150 etc.....

If the data in the worksheets is a consistent format as the one below it may not be horrible. Could you provide a non-sensitive example of your file that contains maybe 3 worksheets..... specifically The portions from C1:ZZ1 that would be needed to take into account your scenario. It will be easier to test out a few different methods with something "close" to the real data set.

David Spears

Copper Contributor

Jun 01, 2018

I have made a test spreadsheet example. I appreciate your helping me.

The data would be consistent. All sheet have the same format and the search parameters would be the same on each sheet. It would go from C1 to PZ1. It would have to count down 16 cells to get all the batch data (C2 thru C17)

TEST.xlsx180 KB

Matt Mickle

Bronze Contributor

Jun 01, 2018

David-

Using your TEST.xlsx file this VBA Code appears to yield the correct results (See Below Code):

End Result after VBA Code is executed:

I have commented the code for better understanding.... it may need to be modified based on the way your file is set up:

Sub GetRecipeCounts()

    Dim ProdLrow As Integer
    Dim ColLp As Integer
    Dim RecipeDay As String
    Dim RecipeCount As Integer
    
    'Define last row on Production sheet based on column A
    ProdLrow = Sheets("PRODUCTION").Cells(Rows.Count, "A").End(xlUp).Row
    
    'Loop through values on the PRODUCTION Sheet in Column A
    For intLp = 2 To ProdLrow
        
        'Define Day to look for...Note this is a strinf because some of your days have -X in them..
        RecipeDay = Sheets("PRODUCTION").Cells(intLp, "A")
        
        'Loop through Worksheets
        For Each Worksheet In ThisWorkbook.Worksheets
            
            'Skip over PRODUCTION worksheet...b/c there is no data on it
            If Worksheet.Name <> "PRODUCTION" Then
                
                '3 to 702 represents Column C to Column ZZ
                For ColLp = 3 To 702
                
                    'Look through first set of values in Row 1
                    'Note we use the left function to get the first 3 characters....
                    If Left(Worksheet.Cells(1, ColLp), 3) = RecipeDay Then
                        'Get count for RecipeDay... note 2 and 17 stand for the rows
                        'ColLp signifies the Column the RecipeDay was found....
                        'We also need to add it to the prior value in case it's the second instance....
                        RecipeCount = RecipeCount + Application.CountA(Worksheet.Range(Worksheet.Cells(2, ColLp), Worksheet.Cells(17, ColLp)))
                    End If
                    
                    'Look through the second set of values on row 28
                    If Left(Worksheet.Cells(28, ColLp), 3) = RecipeDay Then
                        'Get count for RecipeDay... note 29 and 44 stand for the rows
                        'ColLp signifies the Column the RecipeDay was found....
                        'We also need to add it to the prior value in case it's the second instance....
                        RecipeCount = RecipeCount + Application.CountA(Worksheet.Range(Worksheet.Cells(29, ColLp), Worksheet.Cells(44, ColLp)))
                    End If
                    
                Next ColLp
           
            End If
        
        Next Worksheet
        
        'Input the counts to the PRODUCTION worksheet
        Sheets("PRODUCTION").Cells(intLp, "B") = RecipeCount
        
        'Reset the RecipeCount for next Loop....i.e. next RecipeDay
        RecipeCount = 0
    
    Next intLp

End Sub

If you are unfamiliar how to use VBA Code you can use this link as a reference:

http://www.contextures.com/xlvba01.html

Hope this helps. Please let me know if you need further assistance.

Forum Discussion

Formula to search multiple tabs and return a count