Determine difference in time between specific duplicates

Renaissance

Board Regular
Joined
Jun 20, 2013
Messages
56
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>
NameTypeTraitNumberCostTime
JohnBY125$550Sep-13
JohnAY200$300Oct-13
JohnBY300$600Nov-13
JohnBY125$600Dec-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

Renaissance

Board Regular
Joined
Jun 20, 2013
Messages
56
Hey Aladin! What I'm looking for is the number of days/months between duplicate occurrences, beside the specific occurrence.
 

Renaissance

Board Regular
Joined
Jun 20, 2013
Messages
56
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
Joined
May 11, 2010
Messages
612

ADVERTISEMENT

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.
 

Renaissance

Board Regular
Joined
Jun 20, 2013
Messages
56

ADVERTISEMENT

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>
RediA1N1004$477.33Aug-12-213
Redi1X1Y1004198.59Mar-13213

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.

NameTypeTrait Number Cost Time

<tbody>
</tbody>
RediA1N1004$477.33Aug-12-213
Redi1X1Y1004198.59Mar-13213

<tbody>
</tbody>

What are the values you want to see?
 

Renaissance

Board Regular
Joined
Jun 20, 2013
Messages
56
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
TypeTrait
Number
Cost
Time
Difference

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

RediA1N1004$477.33Aug-12-213
Redi1X1Y1004198.59Mar-13213
RediA1N1009623.02Nov-13233
RediN1011623.02Jul-13-123
RediA2N1012$588.20Jan-13-169
RediBY1102$1,001.10Sep-12-123
RediC1N1201$794.30Dec-1291
RediC-1Y1202$931.27Dec-11-377
RediC11207$931.27Nov-11-20
RediY1301752.68Sep-13660
RediA1Y1302$620.11Dec-12-263
Redi1X1Y1305798.99Mar-1391
RediA1N1401$501.42Feb-13-40
RediA21406$710.03Nov-11-448
RediA-2Y1406826.72Dec-13751
RediA1Y1408$689.44Aug-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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top