Is it possible to count date stamped variables acc

Question

I am very new to excel as it pertains to consolidating data and have limited experience with VBA. I have never used pivot tables. I understand that I may need to adjust my table in order to accommodate a solution but prefer not to and am not sure where to start.

In the sample below:

• There are six possible non-numeric, standardized values for each cell in Column F (for the sake of discussion, I have used different types of fruit to represent these various values).

• For each phone contact, a date stamp is generated in column E, and a standardized entry is made in column F.

• At any given time, there are approx 20 worksheets in the workbook. Worksheets are both added and
  omitted on a monthly basis.

• The headers of every worksheet are identical.

• There are up to 500 lines in each worksheet. Thus, the date ranges in each sheet vary.

• The dates in Column E appear in order beginning with the earliest date in Cell E2 on Sheet 1 and the Latest Date in Cell E500 on sheet 20.

I need a solution that will capture the number of apples, bananas, oranges, grapes, lemons, and pears "noted" on each day. Ideally each sheet should have its own respective summary either at the top or bottom of column F as well as a master summary of all sheets at the end of the workbook (or in another workbook). I'm looking for a "dashboard" of sorts. Can this be done? How?

Answer 1

Edit: The code below should now arrange the totals by date.

Private Sub Total()

Dim apple As Integer
Dim banana As Integer
Dim grape As Integer
Dim pear As Integer
Dim lemon As Integer
Dim orange As Integer
Dim sheet As Worksheet
Dim i As Integer
Dim lastRow As Integer
Dim j As Integer
Dim comp1 As String
Dim comp2 As String

apple = 0
banana = 0
grape = 0
pear = 0
lemon = 0
orange = 0


For Each sheet In Worksheets
sheet.Activate
lastRow = WorksheetFunction.CountA(Range("A:A"))
j = 2
'Create the header for the Totals cells
ActiveSheet.Cells(lastRow + 1, 1).Value = "Date"
ActiveSheet.Cells(lastRow + 1, 2).Value = "Apples"
ActiveSheet.Cells(lastRow + 1, 3).Value = "Bananas"
ActiveSheet.Cells(lastRow + 1, 4).Value = "Grapes"
ActiveSheet.Cells(lastRow + 1, 5).Value = "Pears"
ActiveSheet.Cells(lastRow + 1, 6).Value = "Lemons"
ActiveSheet.Cells(lastRow + 1, 7).Value = "Oranges"

For i = 2 To lastRow
    'Compare the date in row i to row i + 1 to see if we should add the totals or start a new daily total
    comp1 = ActiveSheet.Cells(i, 5).Value
    comp2 = ActiveSheet.Cells(i + 1, 5).Value

        'Determine which variable to increment
       If ActiveSheet.Cells(i, 6).Value = "apple" Then
            apple = apple + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "banana" Then
            banana = banana + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "grape" Then
            grape = grape + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "pear" Then
            pear = pear + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "lemon" Then
           lemon = lemon + 1
        ElseIf ActiveSheet.Cells(i, 6).Value = "orange" Then
            orange = orange + 1
        Else
        End If
    If comp1 <> comp2 Then
        'If this is the last entry for this date, past the date and totals
        ActiveSheet.Cells(lastRow + j, 1).Value = comp1
        ActiveSheet.Cells(lastRow + j, 2).Value = apple
        ActiveSheet.Cells(lastRow + j, 3).Value = banana
        ActiveSheet.Cells(lastRow + j, 4).Value = grape
        ActiveSheet.Cells(lastRow + j, 5).Value = pear
        ActiveSheet.Cells(lastRow + j, 6).Value = lemon
        ActiveSheet.Cells(lastRow + j, 7).Value = orange
        'Clear variables for next day
        apple = 0
        banana = 0
        grape = 0
        pear = 0
        lemon = 0
        orange = 0
        'Move to next line for totals row
        j = j + 1

    Else
    End If
Next i

Next sheet

End Sub

This should properly total each page in the manner you are looking for, but it will not create a Grand Total for all pages. You could implement a large array in this code to store all the values between pages, or possibly create a new function using VLookup and some For loops, but it would be rather complex. If possible, I would try to make a formula on your Grand Total sheet to pull the data in.