PDA

View Full Version : Macro to count and give totals



Emoncada
08-24-2012, 12:00 PM
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
08-25-2012, 02:00 AM
Why not just use simple SUMIFS formulae?

Emoncada
08-25-2012, 11:29 AM
How can I get the dates over to te new sheet without duplicates?

Emoncada
08-25-2012, 11:30 AM
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
08-25-2012, 11:58 AM
Post your worksheet I will show you.

Emoncada
08-26-2012, 11:11 AM
Thanks, XLD here it is.

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

PAB
08-27-2012, 09:00 AM
Hi Emoncada,

In cell A1 enter:

01/08/2012
In cell A3 enter the formula:

=A1
In cell A4 enter the formula:

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

=SUM((Completed!$E5:$E1000=B$2)*(Completed!$A5:$A1000=$A3))
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
08-27-2012, 09:13 AM
Hi again Emoncada,

You could actually use:


=SUMPRODUCT((Completed!$E5:$E500=B$2)*(Completed!$A5:$A500=$A3))
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
08-27-2012, 09:33 AM
Hi Emoncada,

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

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

0;-0;;@

I hope this helps!

Kind regards,
PAB

Bob Phillips
08-27-2012, 09:56 AM
Try this

Emoncada
08-27-2012, 11:39 AM
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
08-28-2012, 11:52 AM
Any ideas?

Emoncada
08-29-2012, 09:42 AM
BUMP

PAB
08-29-2012, 05:22 PM
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
08-29-2012, 07:52 PM
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
08-30-2012, 02:19 AM
Hi Emoncada,

You could insert a BUTTON in the Tally Worksheet in say cell E1 and use this code:
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
That's if you use my method, obviously.
I hope this helps!

Regards,
PAB

Emoncada
08-30-2012, 09:04 AM
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
08-30-2012, 09:09 AM
Actually I was able to get this.


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

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

PAB
08-30-2012, 10:33 AM
Hi Emoncada,

You could try this:

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
I hope this helps.

PAB