Job Number Match function???????

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have these excel sheets that need to look to each other and match job number, then, compare values from one to the other and show a True or False

The main "GeneralLog" page has Job numbers in column B11:B61. Here the job number are unique. One the other sheet "Item_Issues" a job umber can repeat many times depending on the number of issues associated with that job number. Now, on the "GeneralLog" each job number has a duration time value associated with it. However, on the "Item_issues" sheet the issues also take up a time value to resolve issues.

What I need to do is, compare the time values per record on the "Items_Issues" sheet related to each job on the "GeneralLog" sheet to verify that the time values per record on the "Items_Issues" sheet per record does not exceed the time value for that particular job number on the "GeneralLog" sheet.. If it does exceed it, show "True", if it does not show "False

Hope this is clear, if further information is needed I'll post more

Thanks,.....
 
Hi

To get a total of the times of the job in B11, assuming the job numbers in
Item_Issues are in A2:A100 and their related times are in B2:B100, use:
=SUMPRODUCT((Item_Issues!A$2:A$100=B11)*(Item_Issues!B$2:B$100))
and format the result as [hh]:mm
You can then fill this formula down for the other jobs.


--
Andy.


Russ said:
I have these excel sheets that need to look to each other and match job
Click to expand...
number, then, compare values from one to the other and show a True or False.
The main "GeneralLog" page has Job numbers in column B11:B61. Here the job
Click to expand...
number are unique. One the other sheet "Item_Issues" a job umber can repeat
many times depending on the number of issues associated with that job
number. Now, on the "GeneralLog" each job number has a duration time value
associated with it. However, on the "Item_issues" sheet the issues also take
up a time value to resolve issues.
What I need to do is, compare the time values per record on the
Click to expand...
"Items_Issues" sheet related to each job on the "GeneralLog" sheet to verify
that the time values per record on the "Items_Issues" sheet per record does
not exceed the time value for that particular job number on the "GeneralLog"
sheet.. If it does exceed it, show "True", if it does not show "False.
 
Hi Russ
some assumptions for both sheets:
- col. B: job number
- col C: duration time

Now enter the following formula in cell D1 on your issue log sheet
=IF(VLOOKUP(B1,'GeneralLog'!$B$11:$C$61,2,0)=>SUMIF($B$1:$B1,B1,$C$1:$C
1),"time not exceeded","time exceeded")
 
Back
Top