I have a simple time sheet. two columns and nine set of three's rows
Column A indicates job/description for example Office - GOW (general office work)
Column B indicates time in/out (using two rows a third for the difference).
What I want to do is combine the job/description if multiples are on that sheet into one total.
For example
I have "Office - GOW" on 3 sets of rows and "Onsite - Mr. Smith Site Survey" in 1 row and "Lunch" in 1 row.
Instead of having to add up the 3 "Office - GOW", I want to have a summary section in the sheet to show only the three items on this sheet (1) Office (2) Onsite (3) Lunch.
Because sometimes, all nine rows are filled with multiple same job/description and sometimes all nine rows are different. I want the summary to show only the different job/description, all the same ones should only show up once and there time difference added together.
I am trying to help accounting to have to go through all the timesheets and add up the same job/description.
Last edited by cpono; 07-14-2006 at 03:59 AM.
add an extra couple of culumns at the end of your current data.
also requires a blank row above the data
for the purpose of this explaination I will assume they are columns F and G
Enter in F2 : =IF(MATCH(A2,A$2:A$n,0)>F1,MATCH(A2,A$2:A$n,0),F1)
replicate down as required.
Note replace n by the number of the last row your data covers.
This will produce a series of numbers that changes for each NEW value in column A, indicating the row at which that value is first encountered.
Enter in G1:
{=INDEX($F$2:$F$n,SMALL(IF($F$2:$F$n<>$F$1:$F$(n-1),ROW($F$1:$F$(n-1))),ROW(1:1)))}
Note ARRAY format thus use ctrl shift enter
replicate down as required.
Note replace n-1 by one less than the number of the last row your data covers.
Enter in A(n+2) (for example) :
=IF(ISNUMBER(G2),INDEX(A$2:A$12,G2),"")
replicate down as required
Enter in B(n+2) :
=SUMIF(A$2:A$12,"="&A16,B$2:B$12)
replicate down as required
Enjoy
Mark
I will let you how it turns out. Much appreciate the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks