Find Duplicate Values in Column, Return Time Between from Different Column

[gdeboer]

This is my first post so please bear with me. I am trying to use some data to pull the lifetime of lithium batteries that I use. My spreadsheet is set up as follows:

Column A: Object Battery is powering (Each object has a number)
Column B: Second Object Battery is powering (if applicable)
Column C: Battery Number (each battery has a number)
Column D: Date the battery was installed
Column E: Last used batter number in the object

I want to put a formula into column G for the following: Find duplicate numbers in column A, and return the distance between the dates in column D for the duplicates.

The hardest part is that there will often be multiple duplicates, and i need the distance between the NEXT duplicate in the row so that i can figure out how long my batteries in my devices are lasting so that i can project replacement costs.

Thank you for the help in advance!

 

[Eric W]

Welcome to the board.

Maybe:

	A	B	C	D	E	F	G
1	Object	2nd Object	Battery #	Date Installed	Last Battery #		Difference
2	1			1-Jan			3
3	2			2-Jan			2
4	3			3-Jan			2
5	1			4-Jan			5
6	2			4-Jan
7	3			5-Jan
8	4			5-Jan
9	5			6-Jan			4
10	6			7-Jan
11	7			8-Jan
12	1			9-Jan
13	5			10-Jan
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula G2 =IFERROR(VLOOKUP(A2,A3:D100,4,0)-D2,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Change the bottom end of the range so that it's after the end of your data.

 

[gdeboer]

Eric,

Thank you so much sir! That worked and was just what I needed. Really appreciate it!

 

[Eric W]

Happy to help!