Find Duplicates In Column A And Calculate Difference Between Times In Column B
Apr 4, 2009
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
View 27 Replies
ADVERTISEMENT
Apr 8, 2014
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
View 7 Replies
View Related
Aug 21, 2013
It will be easier to explain in an example:
A B
Time (hh:mm:ss) Digital
1 10:03:00 0
2 10:03:01 0
3 10:03:02 1
4 10:03:03 1
5 10:03:04 1
6 10:03:05 0
[code]....
From the data above I am searching for the duration of when the digital column says '1', i.e my function = A5-A3 which would output 2 secs or 00:00:03 & likewise A9-A8.
The problem I have is that the digital signal is staggered and does not always have the same frequency. I have over 6848 lines of time to check so to do this manually would take me all day.
View 8 Replies
View Related
Jan 25, 2012
From the below example, I would need to know the difference between date & time of Received and Resolved in number of days. Meaning - time difference should also be calculated in days
CELL ACELL BCELL CCELL DCELL E - REQUIRED OUTPUTReceived
DateReceived
TimeResolved
DateResolved
TimeTurn Around Time - IN DAYS20-Jan-122:43:00 PM23-Jan-124:50:00 PMDifference between Resolved date & time and Received date & time
View 7 Replies
View Related
Jun 13, 2013
I have a huge two column spreadsheet. The first column is a list of times in the format: dd-mm-yy hh:mm:ss and the second column is a status of either START or STOP
Every time there is a START, I want it to count the difference between that time, and the next STOP time, and display the results in column C
Sometimes there will be multiple START's in a row or multiple STOP's in a row, but I am only interested when the status actually changes from the first START to the first STOP. It then resets until there is another START.
Here's an example of how I'd want it to function:
13-06-13 01:22:13 START
13-06-13 01:22:16 START
13-06-13 01:22:20 STOP 00:00:07
13-06-13 02:20.01 STOP
13-06-13 02:21.03 START
13-06-13 03:22.04 STOP 01:01:01
I can do this manually, but of course there is too much data to do that, so I'm looking for an automated solution that looks at the status.
View 1 Replies
View Related
Jan 10, 2007
I am working on an employee weekly schedule and would like to be able to calculate the amount of hours an employee is scheduled each day. For example; if you worked from 7am to 4pm, I want to have a formula that can determine that (7am to 4pm= 9 hours) then sum the total amount of hours for all employees scheduled that day.
Mon Tue Wed Thrs Fri Sat Sun Total
Employee 1 7-4 7-4 8-5 off 2-10 5-10 off 40
Employee 2 8-5 11-8 off 7-4 1-8 off 8-5 43
Hours 18 18 9 9 15 5 9 83
View 2 Replies
View Related
Jun 19, 2008
I have a spreadsheet with Received Date/Time in C2 and Resolved Date/Tim in D2, Work Start Time in E2, Work End Time in F2. C2 and D2 are in dd/mm/yyyy hh:mm AM/PM format:04/03/2008 10:27 AM06/03/2008 1:06 PM9:00:00 AM5:00:00 PM
I need to be able to calculate the resolution time of each request in hours/mins excluding weekends and public holidays. I've tried using the NETWORKDAYS function but it doesn't seem to pick up multiple days between the Received and Resolved dates/times. I have put the Public Holidays in my spreadsheet ( attached) in AA2-AG2.
View 4 Replies
View Related
Aug 22, 2008
I have call data in date/time 06/07/08 2:00 PM custom format for a 2 month period. I have my regular opening times eg Monday 9-3, Tuesday 10-4. For each call I want to know if it was made during opening times or not.
View 7 Replies
View Related
Nov 14, 2008
Need a ormula that will calculate various lengths of time within a column.
For example: I would like to be able to add
02:43 (2 minutes 43 seconds) plus
01:10 (1 minute 10 seconds) plus
05:15 (5 minutes and 15 seconds)
..and accurately arrive at the sum of
09:08 (9 minutes 8 seconds)
Currently, whenever I input the value of 02:43- and assign the value of 'TIME'.. it reads it as 14:23 (clock time..not length of time.) I will only be using data that follows the format of mm:ss (minutes:seconds).
View 2 Replies
View Related
Nov 29, 2012
I am trying to calculate Change for following set. The Calculate field below shows how to arrive at the change. Any logic that I can use to get to Change? Match AccountID and find change in Value from Wk =1 to Wk= 2 without using pivot table option
Wk
AccountID
Value
Change
Calculate
[Code]...
View 2 Replies
View Related
Dec 4, 2008
I'm trying to find duplicates in one particular column. I'm using this formula =if(countif($A$1:$A:$300,A1)=1,FALSE,COUNTIF($A1:A1,A1)=1) in the conditioning format tool but here's my problem:
I'm noticing that some of the cell values have spaces after the entries so the formula is not picking it up as a duplicate. Is there a quick way to remove those unwanted spaces after each entry?
View 9 Replies
View Related
Apr 25, 2008
I have a unique id in column A on a sheet with 20,000 contacts. I need a VBA script to loop through the worksheet and when it sees that there is a duplicate in column A, it needs to take the data from columns B, C, and D of that duplcate and copy them to the first blank cell at the end of the original record. Then it needs to delete the row that contained the duplicate that it just copied. I have searched and found parts of code that will do some of these, but I can't get anything to work, I don't know VB enough.
View 4 Replies
View Related
Apr 20, 2006
In Column A4 I have the word Date and in Columns A5 to A844 are different dates
In Column B4 I have the word Destination and in Columns B5 to B844 are different Destinations
In Column C4 I have the word Time and in Columns C5 to C844 are different times.
How can I find out how many times the word Incoming is in Column B.
How many of the dates in Column A are weekend dates.
How many times in Column C was after 9:00pm but before 6:00am.
View 9 Replies
View Related
Mar 4, 2014
So I have the macro written, but I don't get the complete data that I am looking for. This is what I am trying to achieve
Here is an example of what I am trying to achieve. This is in the first sheet:
Column A Column B
Car Saw
Dew Jacket
Pen Key
Saw Screen
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
Now I want this in the next sheet:
Column A Column B Column C Column D
Car Saw Screen Sock
Car Saw Screen Mouse
Dew Jacket Window
Pen Key
Saw Screen Sock
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
In my current macro, It displays most of what I am looking for, but not a duplicate ID if there is one. For example Screen, there are 2 IDs. My macro is only getting the first one and then skipping over the second ID. I would like it to where if there is a double or if it is there more than once that it gets all of the data.
Here is my macro.
[Code] ....
I will also attach the document of what I am looking for : Data 1.xlsx
View 4 Replies
View Related
Apr 16, 2007
I have many rows of data. How can I find duplicate cells in a column?
View 9 Replies
View Related
Dec 19, 2013
I have a single work book with 8 sheets (I am using Excel 2010 BTW) and I am trying to find a total of times a word appears across all the sheets in column "C"
I found this formula on another thread. =SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C1000"),"="&H3)) with an example. I made the changes that I needed for my purposes
This worked but only after I renamed the sheets to Sheet1, Sheet2, etc.
Is there a way get the same results from the above formula if all the sheets are named after our reps? Example: sheet1 is named Dan, Sheet2 is Nick, etc?
View 2 Replies
View Related
Dec 12, 2013
I have data like this:
NameRank
pietro30
fabri35
mauri60
andrea45
luigi50
anna90
federica100
julia120
Now I want to pick names from column A with rank difference between name should equal to 30.
Expected result
NameRank
pietro30
mauri60
anna90
julia120
View 7 Replies
View Related
Mar 17, 2014
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
View 1 Replies
View Related
Jan 25, 2013
I'm looking to populate my Helper Column with all duplicates from my Data Column. Please note that my helper column needs all instances of a duplicate.
Data Column
Company ABC
Business DEF
Corporation GHI
Business DEF
Company ABC
Business DEF
Helper Column
Business DEF
Company ABC
Business DEF
The formula needs to be dynamic and it will need to be able to be copied down.
View 9 Replies
View Related
Jan 8, 2014
I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.
I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table. I am unable to post the actual document in the interest of data protection.
Essentially, I'd like to turn this-
_ A B
1 Paris Bill
2 Paris Bill
3 Paris Mike
4 Paris Derek
5 Paris Derek
6 London Mike
7 London Bill
8 London Mike
9 London Bill
10 London Derek
11 Tokyo Derek
12 Tokyo Derek
13 Tokyo Derek
into this-
_ A B
1 Paris Bill
2 Paris Mike
3 Paris Derek
4 London Mike
5 London Bill
6 London Derek
7 Tokyo Derek
In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.
View 8 Replies
View Related
Mar 12, 2014
My issue is that I have 1200+ addresses to make more readable - the first column is the street name, the second is the post code(s) relative to the street - what Ideally I'd like to achieve is one row per street, the street name followed by the post codes... i.e. take this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG
Abbey SquareRG1 3BE
Abbey SquareRG1 3BQ
Abbey SquareRG1 3FB
Abbey StreetRG1 3AN
Abbey StreetRG1 3BA
Abbey StreetRG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP
Admirals CourtRG1 6SR
Admirals CourtRG1 6SS
Admirals CourtRG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
to this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG, RG1 3BE, RG1 3BQRG1 3FB
Abbey StreetRG1 3AN, RG1 3BA, RG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP, RG1 6SR, RG1 6SS, RG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
View 5 Replies
View Related
Apr 11, 2008
i have a wordlist which i want to enhance. The wordlist contains of three columns A, B and C. Column A contains of source words, column B of grammtical specifications and column C contain of meanings for source words. The sheet containing this list is named "original" and it looks for example like
View 14 Replies
View Related
May 27, 2014
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
View 3 Replies
View Related
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
May 3, 2013
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
View 5 Replies
View Related
Mar 11, 2013
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
View 9 Replies
View Related
Jan 30, 2014
I have a data set of property sales that represent properties sold twice over a period of time (each address is represented twice in one column but they are 2 different transactions and the remainder of the row data varies). I am looking for a formula or method to remove the duplicates, but I want to remove the duplicate that sold for less.
The entire row is not a duplicate, only the address. There is another column with sale price and I would want it to keep each address of the 2 (or sometimes 3) that is highest. I intend to use a pivot table to analyze and realized that the data is skewed because everything is showing up at least twice.
View 1 Replies
View Related
Aug 3, 2006
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
View 9 Replies
View Related
Mar 6, 2012
I have Column A and B
I want to calculate the total of Column B where by there Column A shows figure "2929".
View 2 Replies
View Related
Feb 27, 2014
In my worksheet Column Y contains multiple duplicates and Column Z contains values associated with each duplicate. I want to check every row in Column Y for multiple duplicates then find the difference between their associated Column Z values.
View 1 Replies
View Related
