Macro to count and give totals

[Emoncada]

I have a spreadsheet "Done" with a lot of information and another Spreadsheet "Tally".
What I'm looking for is the following.
Grab the following data from Sheet "Done"
Column A has a date and Column E has a Ticket Type
Then I have the "Tally" sheet that has column headers as follows

Column A.......Column B...........Column C.......... Column D
Date.............Type 1..............Type 4...............Type 5

So what i need is to be able to have a macro when run to grab the data from "Done" and combine them in "Tally"

So it would show something like this (Example)

Column A.......Column B...........Column C.......... Column D
Date.............Type 1..............Type 4...............Type 5
8/1/12.............4......................1.......................3
8/2/12.............0......................5.......................6

So it will count how many Type 1 there are for each day and give the count, then count how many Type 4's and Type 5's
Then next date etc....

How can i do this?

[Bob Phillips]

Why not just use simple SUMIFS formulae?

[Emoncada]

How can I get the dates over to te new sheet without duplicates?

[Emoncada]

Also the data sheet is always getting new values. If I use sumifs I would need to put that in cells and list constantly is growing.

[Bob Phillips]

Post your worksheet I will show you.

[Emoncada]

Thanks, XLD here it is.

Also note that new lines is constantly being added to Completed sheet, and Tally will be growing.

[PAB]

Hi Emoncada,

In cell A1 enter:

[vba]01/08/2012[/vba]
In cell A3 enter the formula:

[vba]=A1[/vba]
In cell A4 enter the formula:

[vba]=A3+1[/vba]
and copy down as far as is needed.
In cell B3 enter the ARRAY formula below:

[vba]=SUM((Completed!$E5:$E1000=B$2)*(Completed!$A5:$A1000=$A3))[/vba]
and enter with Ctrl-Shift-Enter so you get the curly brackets at each end.
Copy this formula along to cell D3 and down as far as needed.

I hope this helps!

Kind regards,
PAB

[PAB]

Hi again Emoncada,

You could actually use:

[vba]
=SUMPRODUCT((Completed!$E5:$E500=B$2)*(Completed!$A5:$A500=$A3)) [/vba]
in cell B3 and copy this formula along to cell D3 and down as far as needed.
This way you can just enter the formula rather than have to use Ctrl-Shift-Enter to enter it.

[PAB]

Hi Emoncada,

If you wanted to hide the ZERO's you could either use the formula:

[vba]=IF(SUMPRODUCT((Completed!$E5:$E500=B$2)*(Completed!$A5:$A500=$A3))=0,"",SU MPRODUCT((Completed!$E5:$E500=B$2)*(Completed!$A5:$A500=$A3)))[/vba]
OR format the cells as NUMBER CUSTOM:

[vba]0;-0;;@[/vba]

I hope this helps!

Kind regards,
PAB

[Bob Phillips]

Try this

[Emoncada]

XLD when a new line in Completed is added the Tally doesn't grab the new date and values.

How can i get that to go?

[Emoncada]

Any ideas?

[Emoncada]

BUMP

[PAB]

Hi Emoncada,

I don't know if this helps but the Tally sheet is showing the wrong figure for Type 1, it is showing 249 when it should be 259. This is because on the Completed sheet there is the 17/08/2012 & 21/08/2012 but not on the Tally sheet.

I have not used dynamic named ranges before but I have tried changing it with different variations but unfortunately I cannot get it to work.

I hope this helps.

PAB

[Emoncada]

Same here PAB I tried different stuff but ant get it to work.
I would prefer a vba that can accomplish this, but looks like I will have to go with your solution. Only problem with that is I have a list of dates that either have no data or it's a future date.

[PAB]

Hi Emoncada,

You could insert a BUTTON in the Tally Worksheet in say cell E1 and use this code:
[vba]Sub Hide_ZERO_Totals()
Dim c As Range
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
ActiveSheet.Cells.EntireRow.Hidden = False
For Each c In Range("E3:E" & Cells(Rows.Count, 1).End(xlUp).Row)
If c = 0 Then Rows(c.Row).Hidden = True
Next c
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub[/vba]
That's if you use my method, obviously.
I hope this helps!

Regards,
PAB

[Emoncada]

PAB i did a change to make it copy that sheet to a new workbook so I can delete the rows with 0 in E:E how can you modify your code to delete instead of hidden?

[Emoncada]

Actually I was able to get this.


[VBA]Sub DeleteBlankERows()
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 5) = "" Then Rows(r).Delete
Next r
End Sub[/VBA]

Only problem is it takes a while since it needs to go through all rows any ideas how to have it go faster?

[PAB]

Hi Emoncada,

You could try this:

[vba]Sub Delete_ZERO()
Dim LastRow As Long, n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
LastRow = Range("E1000").End(xlUp).Row
For n = LastRow To 3 Step -1
If Cells(n, 5).Value = 0 Then Cells(n, 5).EntireRow.Delete
Next n
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub[/vba]
I hope this helps.

PAB