Find duplicate values in different columns

**excelnewbie1** · 08-05-2012, 08:14 PM

Ok. Is this even possible. I work for an answering service and trying to determine the time between a call being placed and a call returned. Say you have the following values:
callNo time returncall time returned
A1 12:00
A2 1:00
A3 1:34
A4 2:15
3:00 A1 3:00
3:30 A2 2:30
4:00 A3 2:26
4:30 A4 2:15

Say for e.g call A1 was placed at 12:00 and returned at 3:00, so it was 3 hours between the time call placed to returned. And so in, Is there any easy way to do this? I have tried a few fornulas, but nothing has given me what I need.

**FDibbins** · 08-05-2012, 08:24 PM

your thread title and teh requirements you describe seem to be at odds with each other?

if you want to simply subtract 2 times, that is a simple =A1-A2.

if on the other hand, you are looking for when the time when your 12:00 call was returned, what would the criteria be for finding the entry that indicates that cell contains the time it was returned?

**excelnewbie1** · 08-06-2012, 10:07 AM

callNo	time	callreturned	difference
A1	12:00
A2	1:00
A3	1:34
A4	2:15
	3:00	A1	3
	3:30	A2	2:30
	4:00	A3	2:26
	4:30	A4	2:15

So, what it is, is each call is returned at a certain time, and trying to find the difference from when call placed to call returned

**Ace_XL** · 08-06-2012, 10:20 AM

How about in Row 1

=vlookup(A1,C:D,2,false)-A1 and drag down

**excelnewbie1** · 08-06-2012, 10:43 AM

Are you saying A1 as in column A, row 1, or as in callno A1 (because that is just an example). I have 13K rows that I need to go through.

Bascially what I am looking to see if a value in one column is located somewhere in another column. And if it does appear subtract the time from the first column where the value is from the second column where the value is?

**Ace_XL** · 08-06-2012, 10:53 AM

I meant Col A, row 1 and a small tweak to the formula..

=IF(A2="","",(VLOOKUP(A2,C:D,2,FALSE)-B2))

See attached.

**excelnewbie1** · 08-06-2012, 11:10 AM

Not sure if I am explaining this well. I saw your spreadsheet and what I am trying to find is the difference between the 2 times that call was placed and the call was returned.

If one call, A25 was placed at 5:45AM, and A25 was returned at 6:15AM, I want the formula to tell me that it was 0:30 from time was placed to returned. or if another was placed at 8:25AM and the call was returned at 9:54AM, I want the spreadsheet to tell me that it was 1:29 from time call placed to returned.

Does that explain it better?

**Ace_XL** · 08-06-2012, 11:16 AM

That is exactly what my sample worksheet does, dosen't it?

Hence,
Call A1 is 3:00 (12:00 to 15:00)
Call A2 is 1:30 (1:00 to 2:30)
Call A3 is 0:52 (1:34 to 2:26)
Call A4 is 0:00 (2:15 to 2:15)

**excelnewbie1** · 08-06-2012, 11:29 AM

I see what you are trying to get at, but not sure if I follow it correctly,( and I am in now way a excel expert), but the first column is the callno the second is calltime, 2rd is callreturned no, and the 4th gives us the difference of time from when the call was placed and when it was returned (this is the column that I want excel to caluculate).