#1
|
|||
|
|||
compare, match and count cell contents between sheets
I am having a go at building a staff roster with excl 2007, I am stuck and not sure where to start.
|
#2
|
|||
|
|||
A sample sheet would be helpful with a problem as complex as this.
|
#3
|
|||
|
|||
Does it have to be simple?
Perhaps I post what I am working on, there isn't any personal/sensitive info in it. |
#4
|
|||
|
|||
That's fine.
|
#5
|
|||
|
|||
I have uploaded the roster I am working on (I hope );
I am trying to create a roster on the Roster1 page which only contains information/values drawn from the Lists page. On the Roster1 page, cells J9:J35 to AQ9:AQ35 are populated with the shift codes from column F7:F52 on the Lists page, with each row representing a single persons total restored shifts. The column AW9:AW35 on the Roster1 page is intended to display a total restored hours. The problem is, I do not know where to start in creating a solution that works from the Lists page so shift start/finish times can be altered, shifts added/removed etc and the formula will automatically pick up the alterations and display the correct hours rostered total.
P.s. We are using a German version of Excel 2007 so we use ; instead of commas in our formulas. Last edited by bobsone1; 07-28-2014 at 09:16 AM. Reason: German Excal 2007 |
#6
|
|||
|
|||
The equation you use to calculate the time for each shift doesn't work the way you think it does. I think what you are trying to do is to account for those shifts that span midnight and thus would seemingly give negative times if you subtracted the start from the finish. To this end, you add 24 to the end time and then perform the operation. This is both unnecessary and produces a completely erroneous result.
Excel stores dates/times as floating point numbers with each day equal to one and hours/minutes/seconds as appropriate fractions of one. January 0, 1900 (yep, 1/0/1900) is zero and each day thereafter is one more. Thus when you add 24 to a time, you are not adding 24 hours, you are adding 24 days. This will give you incorrect results any time an employee has an N4 shift. For the table you have of the shifts with starts and finishes, it's all fine except for the one shift that spans midnight. All times are in h:mm format. But there is hidden meaning to the data: the start time for F1 of 6am is actually 6am on 1/0/1900. That may sound odd, but for the purpose of your calculations, it is sufficient and changing to any other date, while not affecting the result, is unnecessary. Thus what you need to do is set the time for the end of N4 to be 6:36am on 1/1/1900. You just need to make sure the format is h:mm and the superfluous dates will be invisible and the equations will be seamless. Thus all your equations could be something like: =IF(AND(G7>0,H7>0),H7-G7,"") I've just changed it for those F1 - N4 in the attached file. I did an equation for the first employee. It uses a vlookup for each shift so it's a tad kludgy, but it works as far as I can tell. The hours month to date are just over 100. =COUNTIF(I9:AQ9,"F1*")*VLOOKUP("F1",Lists!F$7:I$52 ,4,FALSE)+COUNTIF(I9:AQ9,"F2*")*VLOOKUP("F2",Lists !F$7:I$52,4,FALSE)+COUNTIF(I9:AQ9,"S1*")*VLOOKUP(" S1",Lists!F$7:I$52,4,FALSE)+COUNTIF(I9:AQ9,"S2*")* VLOOKUP("S2",Lists!F$7:I$52,4,FALSE)+COUNTIF(I9:AQ 9,"S5*")*VLOOKUP("S5",Lists!F$7:I$52,4,FALSE)+COUN TIF(I9:AQ9,"N4*")*VLOOKUP("N4",Lists!F$7:I$52,4,FA LSE) Perhaps someone else can figure out a better way. The two factors, hours and count, lend themselves nicely to a simple sumproduct equation which I've done to the right of the above, but it would require a bit of redesign. There certainly are other ways too. But look this over and let me know if I'm on the right track. If you want we can explore more options. Last edited by gebobs; 07-28-2014 at 03:49 PM. |
#7
|
|||
|
|||
Thanks for the work,
The time solution is more elegant (and reliable) than my effort, something to remember. The countif formula is big isn't it :-), your effort is appreciated. Do you know if there is a way to have a formula that has the ability to automatically account for new/removed shifts e.g. should a 3 hour F3 shift be added (09:00-12:00). Regards |
#8
|
|||
|
|||
Well, there may be a way to use the Sumproduct to do it though it would require a bit of redesign I think. Let me think about it and see if I can't figure out something. At least you can look at it and see if maybe there is a way you can think of to implement it.
|
#9
|
|||
|
|||
Although this is a bit beyond me I will definitely have a go at a sumproduct solution :-)
Thanks again for the effort and input. Regards. |
#10
|
|||
|
|||
Bah...I can't figure it out.
What would be nice if there was some way you could sum those codes and have it sum the times they refer to. That would make adding a code seamless. The equation I have in the previous attachment would require modification if a code is added. Nothing major, but not seamless. |
#11
|
|||
|
|||
I am not getting anywhere, I wonder, is it possible this idea will have to go to the to hard basket?
|
#12
|
|||
|
|||
So; working from the efforts of gebobs combined with a lot of net searches I have come up with this: =COUNTIF(I13:AQ13;"="&Lists!F7)*Lists!I7+COUNTIF.. ....
This solution still needs a specific formula for each shift Code (and calculated Hours) in the Lists sheet. However, because it is using each specific shift Code and calculated shift Hours cells from the Lists sheet it should respond to shift Code and Hours alterations. The next problem I need to solve is to come up with a way to make the formula ignore blank cells so that should a new shift type (e.g. F3 @ 08:00-12:00) be added, the formula wont need to be altered to reflect the addition of a new Code>Hours row. That is, I would like to have the formula incorporate (without having to go back and alter the formula) some unused Code>Hours cells for each shift type (Early, Middle, Late,...) in the Lists sheet. Any Ideas? :-) Regards. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to count entries as per month, year and submission wise from different sheets | pinkacidpunk | Excel | 1 | 05-28-2013 08:26 AM |
If two geographical data match in two sheets, copy unique id/code found in one sheet | alliage | Excel | 1 | 09-01-2011 05:23 AM |
cut&paste cell contents AND formatting | cglenn | Word Tables | 2 | 08-24-2011 07:04 AM |
How to count multiple values in a single cell, except zero? | iuliandonici | Excel | 1 | 04-13-2011 09:45 PM |
Copy all comments & cell contents (i.e. data) to word? | IanM | Excel | 0 | 07-03-2010 11:14 PM |