# Determine difference in time between specific duplicates

#### Renaissance

##### Board Regular
 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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

##### MrExcel MVP
What is the result you want to see?

#### Renaissance

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

#### Renaissance

##### Board Regular
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

##### Well-known Member

 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.

##### MrExcel MVP
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

##### Board Regular

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>

##### MrExcel MVP
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

##### Board Regular
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.

##### MrExcel MVP
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.

Replies
3
Views
99
Replies
20
Views
195
Replies
0
Views
179
Replies
11
Views
253
Replies
3
Views
37

1,130,309
Messages
5,641,447
Members
417,209
Latest member
Agbarker

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back