Using matching data within a list

[Legacy 272348]

I am working on an application that records jobs on a helpdesk as the job is added to, i.e. when the administrator updates it or closes it for example, it creates another line (which comes out into excel as an entry or row in the spreadsheet). Some jobs can have 7/8 lines some can have 3. At the moment I've got 800+ jobs which account for about 4000 lines on my spreadsheet, my difficulty is manipulating the data because of the varying lines (all the jobs have a common job no. next to them) - I'd like to get length job is open, length job is taken to close (each line has a date / time next to it) but because the number of lines per job varies I don't know how to do it in the spreadsheet or a pivot.

Any ideas / urgent help please?

 

[shawnhet]

Your best bet is to post some sample data and what information you are trying to get out of it. I believe that what you ask for will be pretty straightforward but folks here will need a bit more to work with.

Cheers,

 

[Legacy 272348]

Your best bet is to post some sample data and what information you are trying to get out of it. I believe that what you ask for will be pretty straightforward but folks here will need a bit more to work with.

Cheers,

I will do tomorrow, thanks!

 

[Legacy 272348]

I will do tomorrow, thanks!

[TABLE="width: 275"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;" width="111"> <col style="width: 48pt;" span="3" width="64"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]MR No.[/TD]
[TD="width: 111, bgcolor: transparent"]Date[/TD]
[TD="width: 64, bgcolor: transparent"]Fault[/TD]
[TD="width: 64, bgcolor: transparent"]Site[/TD]
[TD="width: 64, bgcolor: transparent"]Priority[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2014 08:26[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2014 08:28[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]06/01/2014 14:02[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]06/01/2014 14:29[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14/01/2014 01:00[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22/01/2014 01:00[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30/01/2014 01:00[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]03/02/2014 13:00[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]03/02/2014 13:00[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]03/02/2014 13:01[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]59467[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]03/02/2014 13:01[/TD]
[TD="bgcolor: transparent"]Isolate Electrics[/TD]
[TD="bgcolor: transparent"]City Campus[/TD]
[TD="bgcolor: transparent"]A[/TD]
[/TR]
</tbody>[/TABLE]


This is how the data is presented; this is one job but basically the jobs all have different numbers of transactions against them and I'd like to get to a point of measure "beginning to end time" per job and forming some diagnostics.

 

[shawnhet]

I think this should work if I understand you correctly - it calculates the Maximum time in B associated with the job from A and subtracts the minimum amount. You will probably want to adjust your formatting so this will work.

=MAX(IF(A2:A12=A14,B2:B12))-MIN(IF(A2:A12=A14,B2:B12)) Array entered Press Ctrl+Shift+Enter not just Enter it will automatically wrap your expression with {} if done correctly.

The above expression assumes that your data is in A2:D12 and that the list of your jobs that you are curious about begins in A14. You can post the above formula into B14 and copy down.

Cheers,

 

[Legacy 272348]

Thanks Shawnhet - that works perfectly EXCEPT the data ranges are not consistent, I only popped one example there but they vary in length, some might be 5, some might be 7 so the data is not in the same place each time. Somehow it needs to look for a key word and then stop or calculate at that point maybe.

Any clues?

 

[shawnhet]

I don't really follow above but perhaps you don't realize that you can alter the A2:A12, B2:B12 ranges to whatever ranges you are working with (ie for your whole data set)?

For instance, if your job numbers are in cells A2:A500 just sub $A$2:$A$500 in my expression for A2:A12 (the dollar signs denote absolute references for when you copy the formula down your list the formula copies as well). You will still need to Array enter the formula.

If that is not your problem, I am going to need more detail in re: what exactly you are looking for.

Cheers,

 

[Legacy 272348]

[TABLE="width: 91"]
<colgroup><col style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;" width="121"> <tbody>[TR]
[TD="width: 121, bgcolor: transparent, align: right"]52982[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52982[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52982[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52982[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52982[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52983[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52984[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52985[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52986[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52987[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52988[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52989[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52990



Shawn, I've pasted a bit more in - as you can see from above the job numbers have varying lines of transaction depending on details and what's being recorded. They do all have a beginning and end with the same piece of text in the cell. The bit I can't grasp is how I can wrap the formula you suggested around given I've got about 4000 job numbers in the spreadsheet? Thanks again for bearing with me.[/TD]
[/TR]
</tbody>[/TABLE]

 

[shawnhet]

I can't see what difference the text will make - so can you try the following:

Copy All of Column A (Mr Nos) into column B on Sheet2 of your spreadsheet.

Select column B on Sheet2 and use Advanced Filter to copy unique records to column A.

http://support.microsoft.com/kb/262277

This will give you a list of all your job nos. listed once. Then you can clear column B and pasted in B2 the following formula (note if the name of the Sheet your source data comes from is not Sheet1 please change accordingly.

=MAX(IF(Sheet1!$A$2:$A$4000=A2,Sheet1!$B$2:$B$4000))-MIN(IF(Sheet1!$A$2:$A$4000=A2,Sheet1!$B$2:$B$4000)) and copy this down your whole range (of unique job nos.).

Now take a look at this output - how specifically is this different from what you were expecting/hoping?

Cheers,