FIRST OCCURRENCE MINUS LAST OCCURRENCE

[IncredibleVolk]

I have a group of accounts with a beginning start time and a beginning end time with duplicates in the range. I need to subtract the earliest (or first) start time from the latest (or last) end time to obtain the total time. The following formula outputs the total time if there's only one instance (no duplicates). How can I subtract the first start time minus the last end time on the account?


=IF(COUNTIF($E$2:$E$3702,E2)=1, H2-G2, "")


ACCOUNT START/TM END/TM TOTAL TIME
4 9:04 9:45
4 9:04 9:46
6 9:01 9:19
6 9:01 9:19
6 9:02 9:22
6 9:02 9:22
6 9:02 9:23
13 9:04 9:46 0:42

 

[IncredibleVolk]

I was able to determine how to find the first and last occurrence. How can I subtract the values derived in each of these formulas?

Display the first occurrence of the start time

=IF(COUNTIF($E$2:$E$3702,E2)=1,FALSE,IF(COUNTIF($E$2:E2,E2)=1,G2,""))

Display the last occurrence of the end time

=IF(COUNTIF($E$2:$E$3702,E2)=1,"",IF(COUNTIF($E$2:E2,E2)=COUNTIF($E$2:$E$3702,E2),H2,"")

This doesn't work

=IF(COUNTIF($E$2:$E$3702,E2)=1,FALSE,IF(COUNTIF($E$2:E2,E2)=1,G2,"")) - IF(COUNTIF($E$2:$E$3702,E2)=1,"",IF(COUNTIF($E$2:E2,E2)=COUNTIF($E$2:$E$3702,E2),H2,""))

 

[IncredibleVolk]

Sorry about the multiple posts. This is working with one exception. It's giving me #VALUE! in the cells that don't qualify.

=IF(COUNTIF($E$2:$E$3702,E2)=1,"",IF(COUNTIF($E$2:E2,E2)=COUNTIF($E$2:$E$3702,E2),H2,"")) - IF(COUNTIF($E$2:$E$3702,E2)=1,"",G2)

 

[SkipVought]

Hi,

1. Use named ranges.

2. on a new sheet in column A, list your accounts...
[tt]
Acct 1 2
4
6
13
...
[/tt]
here is the formula for B2 & C2
[tt]
B2: =MIN(OFFSET(Sheet1!$E$2,MATCH($J2,ACCOUNT,0),B$1,COUNTIF(ACCOUNT,$J2),1))
C2: =MAX(OFFSET(Sheet1!$E$2,MATCH($J2,ACCOUNT,0),C$1,COUNTIF(ACCOUNT,$J2),1))
[/tt]


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[yogia]

Hi IncredibleVolk:

How about ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[IncredibleVolk]

I appreciate the suggestions but neither of these approaches would work. I provided a small sample of a worksheet that can have up to 50,000 records each day. It would take forever to list each individual account. Since this report is based on daily activity, I don't know ahead of time which accounts will appear each day.

 

[GlennUK]

Do a stage of processing before using Yogi's solution, which would be doing a Data/Filter/Advanced Filter on the Account list, choosing to output to a different location ( that would be E2 in Yogi's example ), and choosing the option to output only unique entries.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

"It would take forever to list each individual account"

Not unless your data is totally disorganized...

1. Data > Advanced Filter - Copy to another location/No Criteria/UNIQUE VALUES

2. faq68-5829 Data > Get External Data... Select Distinct... in SQL

Either on could generate a unique list in less than OME MINUTE!!!

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[IncredibleVolk]

That's not what I need. I didn't mention there's other columns in this report. Each start and end time has a specific action associated with it and someone reading the report would need to see the total time in the same row as the either the first or last instance of the listed account.

 

[GlennUK]

How about an array formula ( entered using Ctrl-Shift-Enter ) of:

=IF(MAX(IF($A$2:$A$3702=A2,ROW($A$2:$A$3702),0))=ROW(),INDEX($C$2:$C$3702,MAX(IF($A$2:$A$3702=A2,ROW($A$2:$A$3702)-1,0)))-INDEX($B$2:$B$3702,MIN(IF($A$2:$A$3702=A2,ROW($A$2:$A$3702)-1,9999))),"")

copied down.

Entering a formula as an array formula you will see curly brackets appear around the formula. This is simply an indicator to show that the formula is an array formula ( entered using Ctrl-Shift-Enter instead of Enter ).


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[IncredibleVolk]

That only worked for the first cell. My account is column E, end time column H and start time column G

=IF(MAX(IF($E$2:$E$3702=E2,ROW($E$2:$E$3702),0))=ROW(),INDEX($H$2:$H$3702,MAX(IF($E$2:$E$3702=E2,ROW($E$2:$E$3702)-1,0)))-INDEX($G$2:$G$3702,MIN(IF($E$2:$E$3702=E2,ROW($E$2:$E$3702)-1,9999))),"")


4 9:03 9:45 0:42
4 9:04 9:45
6 9:01 9:19
6 9:01 9:19
6 9:02 9:22
6 9:02 9:22

 

[IncredibleVolk]

Nevermind. I didn't use the CSE correctly. It works! Thanks to all of you for your help.

 

[IncredibleVolk]

GlenUK,

I was wondering if there's anyway to alter this array to give the total time between each start and end times. This is for a call report. This formula is currently providing the difference between the minimum start time and maximum end time on the account. Based on the times below that provides a result of 4 hours 5 minutes. However, we have different people who call on the account throughout the day. Is there anyway to alter the array so the total time is 6 minutes?

ACCOUNT PERSON START TIME END TIME

123 ABC 10:00 A.M. 10:01 A.M.
123 XYZ 2:00 P.M. 2:05 P.M.

 

[yogia]

Hi IncredibleVolk:

I don't see Glen is around at the moment ... so let me see if I have understood you correctly:

Is this what you are looking for?


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[IncredibleVolk]

That's not really working. There are other columns in the report so I need the time to match against column A listed above. Also, the time is actually a recording of an action and we can have actions happening on the account beginning and ending at the same exact time and it only seems to be counting one of them.

123 ABC 10:00:00 A.M. 10:01:30 A.M.
123 ABC 10:00:00 A.M. 10:01:30 A.M.



=IF(SUMIF(F:F,A2,D)-SUMIF(F:F,A2,C:C) > 0,SUMIF(F:F,A2,D)-SUMIF(F:F,A2,C:C),"")

 

[IncredibleVolk]

I think this is on the right track.



=IF(COUNTIF($A$2:$A$1305,A2)=1,D2-C2,IF(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$10676,A2),D2-C2,""))

 

[GlennUK]

Hi IncredibleVolk,

sorry, it was the first day back at work after holidays yesterday, so rather busy. Will have a look at this tonight, hopefully.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

How about;

=IF(COUNTIF($A$2:$A$1305,A2)=1,D2-C2,IF(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$10676,A2),SUMPRODUCT(($A$2:$A$1305=A2)*($D$2:$D$1305-$C$2:$C$1305)),""))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[IncredibleVolk]

GlennUK,

That worked great. Thanks again and I appreciate everyone's input. You're showing me things I never knew Excel could do.

 

[GlennUK]

My pleasure :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.