Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
I have a spreadsheet which contains Work Order numbers, craft code, work
order date and estimated hours.There are also other non-relevant cells. These items are sorted by a controller who decides which priority they are completed in. When the job is completed, a clerk enters the actual hours taken to do the job by craft.This report is exported into Excel and has the same data as the original except it also has the actual hours by date by craft. As there are no indexed fields I have concatenated the work order number with the craft code as this only shows once per work order. My task - should I choose to accept it :) - is to try and allocate the actual hours by work order by craft code by date.There are thousands of work order but I have shown a couple as an example. A B C D E 04/01/06 04/02/06 04/03/06 and so on wo# Craft Est Hrs Date Act Hrs Act Hrs Act Hrs 123456 10 4 04/01/06 ? ? ? 256354 20 3 04/02/06 ? ? ? This is always for 5 working days Mon-Fri. I concatenate the wo# with the craft code as follows: =(VLOOKUP(CONCATENATE($D4,$E4),$M$4:$P$6,4,0)) Where M4:P6 contains the range of work orders already concatenated as wo# and Craft Code hence line one would be 12345610 and so on. Col 4 of the lookup is the actual hours. Now this works fine for one column but how do I get the date separated so that the actual hours are shown in the right date? The data I am pulling from is as follows: WO# / Craft Date Act Hours 12345610 04/04/06 3 25635420 03/04/06 5 Thanks in advance for any help. Nigel Forge |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
How about a pivot table with Date on the left & WO/Craftcode underneath it, and Sum of hours in the field? Alternatively, if you are doing one week at a time, WO on the left, Date across the top, and Sum of Hours in the data field. ---GJC -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=529658 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
No - won't work as the spreadsheet I am populating is a template required for
all sites across the group but we are new to the group. Thanks for the idea though. Nigel "gjcase" wrote: How about a pivot table with Date on the left & WO/Craftcode underneath it, and Sum of hours in the field? Alternatively, if you are doing one week at a time, WO on the left, Date across the top, and Sum of Hours in the data field. ---GJC -- gjcase ------------------------------------------------------------------------ gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061 View this thread: http://www.excelforum.com/showthread...hreadid=529658 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
In general this is a problem of "SUMIF with Multiple Criteria" as it is
often asked in the newsgroups. The solution is the SUMPRODUCT function: =SUMPRODUCT((A1:A100=123456)*(B1:B100=10)*(D1:D100 =--"04/04/06")*(C1:C100)) You can replace the constants with cell references. If so, you only need to specify the cell for the date, i.e. D1:D100=K1, where K1 holds the date. HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Try this
=IF($D3=E$1,VLOOKUP(($A3&$B3)*1,table,3,FALSE),0) A B C D E F 1 4/3/2006 4/4/2006 2 WO Craft Est Hrs Date Act Hrs Act Hrs 3 123456 10 4 4/3/2006 3 0 4 256354 20 3 4/4/2006 0 5 TABLE WO# Craft Date Act Hours 12345610 4/3/2006 3 25635420 4/4/2006 5 "Nigel Forge" wrote: I have a spreadsheet which contains Work Order numbers, craft code, work order date and estimated hours.There are also other non-relevant cells. These items are sorted by a controller who decides which priority they are completed in. When the job is completed, a clerk enters the actual hours taken to do the job by craft.This report is exported into Excel and has the same data as the original except it also has the actual hours by date by craft. As there are no indexed fields I have concatenated the work order number with the craft code as this only shows once per work order. My task - should I choose to accept it :) - is to try and allocate the actual hours by work order by craft code by date.There are thousands of work order but I have shown a couple as an example. A B C D E 04/01/06 04/02/06 04/03/06 and so on wo# Craft Est Hrs Date Act Hrs Act Hrs Act Hrs 123456 10 4 04/01/06 ? ? ? 256354 20 3 04/02/06 ? ? ? This is always for 5 working days Mon-Fri. I concatenate the wo# with the craft code as follows: =(VLOOKUP(CONCATENATE($D4,$E4),$M$4:$P$6,4,0)) Where M4:P6 contains the range of work orders already concatenated as wo# and Craft Code hence line one would be 12345610 and so on. Col 4 of the lookup is the actual hours. Now this works fine for one column but how do I get the date separated so that the actual hours are shown in the right date? The data I am pulling from is as follows: WO# / Craft Date Act Hours 12345610 04/04/06 3 25635420 03/04/06 5 Thanks in advance for any help. Nigel Forge |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Thanks Kostis - that looks better!
Nigel "vezerid" wrote: In general this is a problem of "SUMIF with Multiple Criteria" as it is often asked in the newsgroups. The solution is the SUMPRODUCT function: =SUMPRODUCT((A1:A100=123456)*(B1:B100=10)*(D1:D100 =--"04/04/06")*(C1:C100)) You can replace the constants with cell references. If so, you only need to specify the cell for the date, i.e. D1:D100=K1, where K1 holds the date. HTH Kostis Vezerides |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Hi Kostis
Having used the function it only returns a 1 to show that there is dat there and not the actual number of the hours which is what I need. Any ideas please? Thanks again Nigel "vezerid" wrote: In general this is a problem of "SUMIF with Multiple Criteria" as it is often asked in the newsgroups. The solution is the SUMPRODUCT function: =SUMPRODUCT((A1:A100=123456)*(B1:B100=10)*(D1:D100 =--"04/04/06")*(C1:C100)) You can replace the constants with cell references. If so, you only need to specify the cell for the date, i.e. D1:D100=K1, where K1 holds the date. HTH Kostis Vezerides |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Nigel,
Consider again the formula: =SUMPRODUCT((A1:A100=123456)*(B1:B100=10)*(D1:D100 =--"04/04/06")*(C1:C100)) As you see it multiplies 4 components. The first three are conditions and the last one is the data that you want the conditional sum of. I don't know exactly in which column you have what, I assumed it is in these columns from your posts. The important thing is that if you omit the last component, with the amounts, this formula will act as a counting formula, i.e. it will tell you how many records have 123456 in WO, 10 in Craft and 04/04/06 in Date. Each one of the components will produce a True or False. Multiplied, these turn to 1's or 0's, you will get 1' only if all conditions are true. These 1's, multiplied with the amounts, will give you a conditional sum. There is not much more I can say here, these are formulas that are very often used in such cases. Just reexamine your ranges and make sure you include the amount. Write back if you have something more specific. HTH Kostis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |