Tally my bananas! :-)

[Mr. Haney]

This was brought over from the 'functions' group.

I think I need a combination of a sub, and the use of SUMPRODUCT.

Any ideas?



OK... Here is what I want, and it boils down to me not knowing how to
handle the test code, not so much the tally.


I have a sheet with data that starts with a text column that has job
numbers in it.

The sheet is a fixed length (number of rows), meant to be printed out
on a weekly basis,
so there are always blank lines on the sheet that have no data in this
first aforementioned column.


Job Number Mon Time Start Mon Time End Tues Start Tues End

TextSamp1 06:00 08:30
TextSamp2 08:30 10:30
Meal 10:30 11:30
TextSamp1 11:30 15:30
TextSamp2 15:30 17:30

With several blank lines following these

I want to do a "For Each" non-blank example of job number, SumProduct
the time accruals, skipping the blank lines.

With a result that lists the job numbers worked in that day, and the
total time put toward that job number.

Job Number Mon Time Total

TextSamp1 6.5 Hrs
TextSamp2 4.0 Hrs
Meal 0.5 Hrs

I know this is easy, but I just cannot find an example for what I want.

I will do it on a separate tally sheet, and perhaps even from a
different workbook where I query the date coded file names as:

task_track_041809.xls for the week of 04-18-09.

It would seem that I could do it on an included sheet macro free, but
the separate workbook would require VB code, but I am not sure. They
both might need it to perform the test criteria cycling.


Thank you for any assistance in this endeavor.

 

[Peter T]

Good title !

There are several approaches, but before doing anything better to include
additional helper columns for totals. Probably best to have a separate col
for each day, eg

say you have Mon Start/End times in B2:C2
=IF(C2,C2-B2,0)

Then include a grand total column to sum the day totals.

Say your JobNumbers are in col-A and Grand Total in col-H (probably it'll be
further to the right)

Select data in col-A, Data/Filter/Advanced Filter
Copy to another location and check unique records

copy to say J1

Hopefully you've now got a unique list in col-J
With Grand Totals in col-H and unique Jobs in J,
in K2: =SUMPRODUCT(--($A$2:$A$7=J2)*$H$2:$H$7)
and copy down

Better to use Local worksheet names for those references, eg
Sheet1!Jobs and Sheet1!TTime

You could record a macro to do the advanced filter (use the name Jobs for
the advanced Filter list), and populate the formulas.

Of course there are other ways, perhaps the most obvious is a Pivot Table
(if you end up with 'Count of' rt-click the counted data, Field settings and
choose Sum)

Regards,
Peter T


most obvious that comes to mind is a PivotTable

 

[LOFE]

I'd write some code that can be re-used.

I'm assuming that until the lines become blank the rows will all contain
data. And there is something about extra sheets in there but I'm not too
sure if that is the case.

You can also add code to open a file to be used. But this is just the data
extraction and dump.

Range("A1").Select
Sheets("Data").Select 'Assuming sheet name here
Do Until Activecell = "" 'If the report is fixed length, this could be Do
Until Activecell.Row = 5000
If Activecell <> "" Then
MyJob = Activecell
MonTime = Activecell(1,3) - Activecell(1,2)
TuesTime = Activecell(1,5) - Activecell(1,4)
'...keep going for remaining days
Sheets("Results").Select 'Assuming there is a results sheet otherwise one
would have to be added
Activecell = MyJob
Activecell(1,2) = MonTime
Activecel(1,3) = TuesTime
'...keep going for remaining days
Activecell(1,8) = MonTime + TuesTime + .......
Activecell(2,1).Select
Sheets("Data").Select
End If
Activecell(2,1).Select
Loop

 

[Mr. Haney]

First off, thank you for your assistance.

My sheet is one portrait style page in size, with 32 rows.

They are ALL blank UNTILL I add a job number and start time.

I already have tally columns that tally time on a per day basis for
each LINE ENTRY (row), and I already have a column that tallies the
entire per row weekly totals.

So my sheet already shows the total hours worked in the week, and the
total hours worked each day for each row entry.

The thing I am looking to do is due to the fact that I can jump around
on different jobs throughout the day, and I need to tally on a per job
basis that involves testing each row for the job number and totallizing
<sic> all the time for a given job on a per day basis.

So I have made a text representation of my sheet (very basic though) and
my goal.

I have described the cell types just under the header row.

Job Number Time in Time Out Time Total
AlphaNum1 24Hr 24Hr Per Line Tally (Monday example)
(Monday) (Monday)


SampJob1 07:00 09:00 2.0
SampJob2 09:00 11:00 2.0
SampJob3 11:00 12:00 1.0
Daily Meal 12:00 12:30 0.5
SampJob1 12:30 13:30 1.0
SampJob2 13:30 14:30 1.0
SampJob3 14:30 16:30 2.0



(the remainder is blank lines which are there in case I have a VERY busy
day.) Also, to the right each row gets totaled just fine, but that same
job may get work done against it again, later in the day, so I need to
scan the whole sheet for each jobnum instance, then tally all instances
in a given day for that Job number.


Goal: Ignoring blank entries at Job Number and the
"Daily Meal" entry

Tally as per job for total time accrued in a given (selected) day.


Job Number Time Total

SampJob1 3.0
SampJob2 3.0
SampJob3 3.0


So FOR each instance of JobNumber, I would scan that day's column for
additional entries of that job number and tally them into a final tally
(report) sheet.

I will then copy the code into seven sheets, one for each day.

I figured that SUMPRODUCT would be good, as I can then scan a sheet in
a workbook that is not even open. And make a workbook specifically for
the tally report operations.

 

[Peter T]

Did you try what I suggested. If you adapt according to your needs I think
it should accomplish what you are looking to achieve.

Regards,
Peter T

 

[Mr. Haney]

Did you try what I suggested. If you adapt according to your needs I think
it should accomplish what you are looking to achieve.

Your reply was suggesting tallying my time per day per line, which I
ALREADY DO.

All I want to know is the function statement along the lines of a For
Each in the job number column that also tests for the blank line in that
column and the daily meal entry and only grabs the job number entries,
THEN tallies all entries of that number.

You appear to be thinking that I cannot tally time, which I am doing
just fine.

What I am after is due to the fact that I have MULTIPLE like entries of
a given job number in a given day. My daily total of all jobs done in a
day works fine. My weekly total of all work done in a week works fine.

What I want is to tally EACH job in each day in a circumstance where
said job will appear more than once.

This whole thing would work far better if you guys would put up a
newsgroup that accepts binary posts that get pre-approved for posting.
Make it a moderated group such that no pre-approved posts show up, and
you can then see what I am doing.

The text I posted appeared descriptive enough for me, as it clearly
shows that I am already tallying my per line time. If you examine it
carefully, you will see that the same job number appears more than once.

I saw nothing in your code that would tally per unique entry in the
first column.

Maybe I missed something when I examined it, but it appeared as if you
are thinking that I want to tally a per line time, which I already do.

It would be far better if I could actually send you the workbook,
because I intend to post it as a template once it is perfected.

So, no I didn't try to adapt your code as it looked like you missed
what my goal is. Maybe I didn't examine it closely enough, but I got the
feeling (and again now) that you didn't catch what I am after.


My sheet, in a bit more exact example: B= Begin E = End t= entry total

J/N MB ME t TB TE t WB WE t ThB ThE t FB FE t SB SE t SuB SuE t WeekTotal
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | |

There are seven per entry totals there and one full week total, per row.

There is a row at the bottom that does a per day total, and a cell that
tallies the entire week's hours.

The problem is that the same job will appear more than once in a given
day (multiple row entries, same job). My example showed sample1 2 and 3
all showing up more than once.

So I want to do a tally sheet that shows job numbers and total time for
a given day..

So I already tally per line (full week) totals, and I already tally on
a per day columnar total. Between each day's entry is also a total
column for each time entry.

So disregarding the empty lines, I get an array that shows how long I
worked each task, with "task" meaning ANY job. The fact that I bounce
around from campus to campus means that yet another entry of any given
job in a given day will be very likely to occur.

This is either an easy simple excel function I can put in a cell on
another sheet, OR I need to code a sub for it. I think that due to the
multiple instances of a given job that I will need some code to test for
additional entries of a given job.

If your code performs that, I didn't see it, but if it does, just let
me know, and I will go through it again.

Also, Thank you again for your help, as I know about what I want, but
am not familiar enough with VB or excel macro functions to do it without
help.

 

[Peter T]

Your reply was suggesting tallying my time per day per line, which I

ALREADY DO.

No. You appear to have misread or misunderstood what I attempted to suggest.
There were two parts to the suggestion

1. Extract a unique list of "jobs"
2. Using Sumproduct get the totals for each "job"

The combined example and explanations I provided (incl use of helper
columns) would have turned something like this

TextSamp1 06:00 08:30
TextSamp2 08:30 10:30
Meal 10:30 11:30
TextSamp1 11:30 15:30
TextSamp2 15:30 17:30

into this

TextSamp1 6.5 Hrs
TextSamp2 4.0 Hrs
Meal 0.5 Hrs

If I follow your objective it's a very common task in Excel, there are all
sorts of other ways of going about it, including sorting things out with
code. However there's not much point to start giving code examples without
further detail from yourself.

If that is not the objective maybe it's me who has misread or misunderstood
what you have tried to describe.

Regards,
Peter T

 

[Mr. Haney]

If I follow your objective it's a very common task in Excel, there are all
sorts of other ways of going about it, including sorting things out with
code. However there's not much point to start giving code examples without
further detail from yourself.

Yes, I know it is common.

I will reduce it to a basic exercise (below) for someone that IS
familiar with the code, as I am not quite familiar enough, which is why I
came.

You were giving me MonTime + TueTime + WedsTime +... etc. Which is NOT
what I was after. I already got that.

As far as detail goes, I could not have been more concise.

Four columns, Forget what day of the week is being referred to as that
is not important in obtaining the code or function segment that will be
the basis for this engine..

A column is "UniqueID". Multiple instances of this ID are expected, and
are the goal of the sorted 'report table'.

B column is start time, though not even important as the totals are all
that need to be tallied here.

C column is finish time.

D column tallies the two time entries on each given row as a per entry
total of B + C.

On a separate sheet, or workbook, that four column array needs to be
tallied just like my result need that was given as well as the
interpretation you gave for the final 'report' appearance. To columns of
UniqueID and TotalofTallies(UniqueID).

I was simply under the impression that what you responded with appeared
to be far more than I needed. This single pass recursive test engine
would seem to be only a few lines of code...

...or even a single "function" which I could apply to each uniqueID
example as a "button" that tests the sheet for that unique id and dumps
the answer in a cell on another sheet.

 

[Peter T]

someone that IS familiar with the code

OK, as far as you are concerned I am not familiar with code

To columns of
UniqueID and TotalofTallies(UniqueID).

I suggested how to achieve exactly that, specifically,
UniqueID - Advanced filter
TotalofTallies(UniqueID) - Sumproduct

Just to be sure I, and others who may be able to help you more, understand
the objective, try this simple example

in A1:B5
ID units
apple 1
pear 2
apple 3
pear 4


in C1:C3
UniqueID
apple
pear


in D2: =SUMPRODUCT(--($A$2:$A$5=C2)*$B$2:$B$5)
copy D2 down to D3.

You should get results 4 & 6, TotalofTallies(UniqueID)
Is that equivalent to what you are looking for

Regards,
Peter T

 

[Mr. Haney]

OK, as far as you are concerned I am not familiar with code


I suggested how to achieve exactly that, specifically,
UniqueID - Advanced filter
TotalofTallies(UniqueID) - Sumproduct

Just to be sure I, and others who may be able to help you more, understand
the objective, try this simple example

in A1:B5
ID units
apple 1
pear 2
apple 3
pear 4


in C1:C3
UniqueID
apple
pear


in D2: =SUMPRODUCT(--($A$2:$A$5=C2)*$B$2:$B$5)
copy D2 down to D3.

You should get results 4 & 6, TotalofTallies(UniqueID)
Is that equivalent to what you are looking for

Regards,
Peter T

Yes, That is exactly what I want, EXCEPT that you "manually" construct
the list at C1 thru C3. The code needs to construct it.

I want the code to test the entire column, and return the list. It
seems to be a simple If not = "" or such to disregard the blank lines,
but any lines it does find need to be tallied in the result table, but
not just tallied separately, any like entries need to have a single entry
in the result sheet, yet tally all entries on the data array sheet
tested.

So, I need to test and construct the list, and *then* populate it with
totallized tallies (is that redundant?).

 

[Mr. Haney]

Yes, That is exactly what I want, EXCEPT that you "manually" construct
the list at C1 thru C3. The code needs to construct it.

I want the code to test the entire column, and return the list. It
seems to be a simple If not = "" or such to disregard the blank lines,
but any lines it does find need to be tallied in the result table, but
not just tallied separately, any like entries need to have a single entry
in the result sheet, yet tally all entries on the data array sheet
tested.

So, I need to test and construct the list, and *then* populate it with
totallized tallies (is that redundant?).

I have succeeded in using a pivot table to view totallized results, but
I still think there is a much more simple way to tally once, each entry
for a given task in a given day(vertical) without the need for a pivot
table. and without the need to perform any manual list creation or update
one. Since the "table" I want to tally (the time sheet) has a fixed,
limited number of rows, one would think that the entire array could be
polled fairly easily. How long can a formula string be?