Determine difference in time between specific duplicates

[Renaissance]

Hey Guys, in the example below I am looking primarily at columns D (Number) and F (Time). What I hope to accomplish is to search for the duplicates (125 in this example) and count the time difference between them. I'm not concerned about where I need to place this information, and I'm assuming I will need at least one or two columns. My question is: How do I do this? A combination of =Countif($D$2:$D$5,$D5)>1 and something else? I am curious how long a replacement is lasting, which would be indicated by more than a single replacement per individual "Number".

<tbody>
</tbody>

Name	Type	Trait	Number	Cost	Time
John	B	Y	125	$550	Sep-13
John	A	Y	200	$300	Oct-13
John	B	Y	300	$600	Nov-13
John	B	Y	125	$600	Dec-13

<tbody>
</tbody>

Thanks in advance for taking the time to help me out. I'm thinking there is a simple solution, but I'm not sure how to best tackle this.

 

[Aladin Akyurek]

What is the result you want to see?

 

[Renaissance]

Hey Aladin! What I'm looking for is the number of days/months between duplicate occurrences, beside the specific occurrence.

 

[Renaissance]

Not "beside the specific occurrence", but either next to the specific occurrence, or in a separate table somewhere. As long as I can trace the time difference to the specific "Number".

 

[Trouttrap2]

Hey Guys, in the example below I am looking primarily at columns D (Number) and F (Time). What I hope to accomplish is to search for the duplicates (125 in this example) and count the time difference between them. I'm not concerned about where I need to place this information, and I'm assuming I will need at least one or two columns. My question is: How do I do this? A combination of =Countif($D$2:$D$5,$D5)>1 and something else? I am curious how long a replacement is lasting, which would be indicated by more than a single replacement per individual "Number".

<tbody>
</tbody>

Name	Type	Trait	Number	Cost	Time
John	B	Y	125	$550	Sep-13
John	A	Y	200	$300	Oct-13
John	B	Y	300	$600	Nov-13
John	B	Y	125	$600	Dec-13

<tbody>
</tbody>

Thanks in advance for taking the time to help me out. I'm thinking there is a simple solution, but I'm not sure how to best tackle this.

Try {=LARGE(IF(H1=D2:D5,F2:F5),1)-LARGE(IF(H1=D2:D5,F2:F5),2)}

H1 houses the Number Lookup (125). Assumes that your data is in columns A1:F5. Formula must be Ctrl+shift+enter because its an array formula. This also asumes that you don't have more than 2 duplicates in the Number field.

 

[Aladin Akyurek]

Hey Aladin! What I'm looking for is the number of days/months between duplicate occurrences, beside the specific occurrence.

G2, copied down:

=IFERROR($F2-LOOKUP(9.99999999999999E+307,1/($D$1:D1=$D1),$F$1:F1),0)

Format the formula cell as General.

 

[Renaissance]

G2, copied down:

=IFERROR($F2-LOOKUP(9.99999999999999E+307,1/($D$1:D1=$D1),$F$1:F1),0)

Format the formula cell as General.

Here is an example of what I get when the duplicates are sorted next to each other. However, when I have them sorted by "Time", the numbers are drastically different. For example, they do not go over 40, and are in (seemingly) random places, and never beside the duplicates.

Name

Type

Trait

Number

Cost

Time

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Redi	A1	N	1004	$477.33	Aug-12	-213
Redi	1X1	Y	1004	198.59	Mar-13	213

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Here is an example of what I get when the duplicates are sorted next to each other. However, when I have them sorted by "Time", the numbers are drastically different. For example, they do not go over 40, and are in (seemingly) random places, and never beside the duplicates.

Name

Type

Trait

Number

Cost

Time

<tbody>
</tbody>

Redi	A1	N	1004	$477.33	Aug-12	-213
Redi	1X1	Y	1004	198.59	Mar-13	213

<tbody>
</tbody>

What are the values you want to see?

 

[Renaissance]

213 is accurate and that is what I'm looking for, but when I sort it by "Time" which is how I input the data, then it changes. Is there a way to alter the formula so regardless of the position in rows, it would calculate the same?

Also, I don't know if this will help, but I will copy/paste some of the other rows to show you how it is calculating. I don't know what the first two rows I have shown are correct, while the rest of them seem to calculate based on the row above, and not necessarily because of a duplicate.

Name

Type

Trait

Number

Cost

Time

Difference

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Redi	A1	N	1004	$477.33	Aug-12	-213
Redi	1X1	Y	1004	198.59	Mar-13	213
Redi	A1	N	1009	623.02	Nov-13	233
Redi		N	1011	623.02	Jul-13	-123
Redi	A2	N	1012	$588.20	Jan-13	-169
Redi	B	Y	1102	$1,001.10	Sep-12	-123
Redi	C1	N	1201	$794.30	Dec-12	91
Redi	C-1	Y	1202	$931.27	Dec-11	-377
Redi	C1		1207	$931.27	Nov-11	-20
Redi		Y	1301	752.68	Sep-13	660
Redi	A1	Y	1302	$620.11	Dec-12	-263
Redi	1X1	Y	1305	798.99	Mar-13	91
Redi	A1	N	1401	$501.42	Feb-13	-40
Redi	A2		1406	$710.03	Nov-11	-448
Redi	A-2	Y	1406	826.72	Dec-13	751
Redi	A1	Y	1408	$689.44	Aug-12	-476

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Other than the Number "1004" as previously mentioned, the only other duplicate in this example is 1406 (2nd and 3rd from last rows), and while the 751 is the accurate number of days between duplicates, it appears as if the -448 is calculating the difference between Nov-11 and Feb-13.

 

[Aladin Akyurek]

213 is accurate and that is what I'm looking for, but when I sort it by "Time" which is how I input the data, then it changes. Is there a way to alter the formula so regardless of the position in rows, it would calculate the same?

[...]

The formula has an unintended spec...

=IFERROR($F2-LOOKUP(9.99999999999999E+307,1/($D$1:D1=$D2),$F$1:F1),0)

That is, $D1 --> $D2 after the equal sign.