On Mar 25, 7:00*pm, joel wrote:
I though of a differnt solution. You also may be having problems if some fof
the number are in a fraction of a minute. *I'm going to use the FORMAT
function to extract the just the hours and minutes from the time

from:
If UCase(Trim(Range("A" & RowCount))) = UCase(Trim(Range("A" & (RowCount +
*1)))) Then

to:

If format(Range("A" & RowCount),"HH:MM") = _
* *Format(Range("A" & (RowCount + 1)),"HH:MM") Then



"MattLC" wrote:
On Mar 25, 12:26 pm, joel wrote:
Some of the cells have the date included in the time. *Lets try rremoving the
date

from:
If UCase(Trim(Range("A" & RowCount))) = UCase(Trim(Range("A" & (RowCount +
*1)))) Then

If Mod(Trim(Range("A" & RowCount)),1) = Mod(Trim(Range("A" & (RowCount +
*1))),1) Then

"MattLC" wrote:
On Mar 25, 10:47 am, joel wrote:
The formating will have nothing to do with the issue, the data itself doesn't
match. *Usually it is because there are extra spaces in the data or the data
isn't capitlized the same. *try this chage

from:
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) Then

to:
If UCase(Trim(Range("A" & RowCount))) = UCase(Trim(Range("A" & (RowCount +
1)))) Then

"MattLC" wrote:
On Mar 25, 10:25 am, joel wrote:
Try this code it is much simplier. *From what I can tell of you code your
offsets were wrong. *You had column offsets instead of row offsets in some
cases.

Sub TimeX()

Dim TimeInv
Dim cnt As Long
Dim total As Long

'sort the data
Range("A1").Activate
ActiveCell.CurrentRegion.Sort _
* *Key1:=ActiveCell, _
* *Order1:=xlAscending, _
* *Header:=xlYes, _
* *DataOption1:=xlSortTextAsNumbers

'Start at the second row - assumes will always be the first time interval
Range("A1").Activate
TimeInv = ActiveCell.Value * 'get the first range in the sheet;
* * * * * * * * * * * * * * *'assumes info starts at A1

RowCount = 2
Start = RowCount
Do While Range("A" & RowCount) < ""
* *If Range("A" & RowCount) = Range("A" & (RowCount + 1)) Then
* * * Range("A" & RowCount) = _
* * * * *Range("A" & RowCount) + Range("A" & (RowCount + 1))
* * * Rows(RowCount + 1).Delete
* *Else
* * * RowCount = RowCount + 1
* *End If
Loop

End Sub

"MattLC" wrote:
All,

It may be easier to provide an example oppose to trying to explain
this issue.

Currently I have hundereds of rows that look like this.
Column A * * * Column B
1:00 * * * * * * * * * * 1
1:15 * * * * * * * * * * 1
1:15 * * * * * * * * * * 2
1:15 * * * * * * * * * * 2
1:45 * * * * * * * * * * 0
2:00 * * * * * * * * * * 0
2:00 * * * * * * * * * * 0

The End Result should look like this;

Column A * * Column B
1:00 * * * * * * * * * 1
1:15 * * * * * * * * * 5
1:45 * * * * * * * * * 0
2:00 * * * * * * * * * 0

Here is the macro I currently have and for some reason its not
combining ALL of the times.. just most. *I can send the attachment if
needed, here is the current macro. *It may be a formatting issue with
the cells, therefore my second question is -- Is there an easy way to
make all cells in a column be the same format? (Right Click-- Format
Cells doesnt do the trick).

Sub TimeX()

Dim TimeInv
Dim cnt As Long
Dim total As Long

'sort the data
Range("A1").Activate
ActiveCell.CurrentRegion.Sort Key1:=ActiveCell, Order1:=xlAscending,
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

'Start at the second row - assumes will always be the first time
interval
Range("A1").Activate
TimeInv = ActiveCell.Value * 'get the first range in the sheet;
assumes info starts at A1

While ActiveCell < "" *'while the current cell is not blank -
pAUSesses until it hits a blank cell

* * If ActiveCell.Value < TimeInv Then
* * * * * * ActiveCell.EntireRow.Insert *'insert a row and write out
values
* * * * * * ActiveCell.Value = TimeInv
* * * * * * ActiveCell.Offset(0, 1).Activate
* * * * * * ActiveCell.Offset.Value = total
* * * * * * total = 0 'reset total
* * * * * * cnt = 0 'reset cnt
* * * * * * ActiveCell.Offset(1, -1).Select 'go to the next row
* * * * * * TimeInv = ActiveCell.Value
* * * * Else
* * * * * * 'capture count in varibles, then delete the row
* * * * * * cnt = ActiveCell.Offset(0, 1).Value
* * * * * * total = cnt + total
* * * * * * ActiveCell.EntireRow.Delete
* * End If

Wend

End Sub- Hide quoted text -

- Show quoted text -

Thanks for the response Joel,

I am still having an issue of it not combining ALL of the intervals, I
think this is due to the way the cells are formatted. *Is there a way
to "reset" the cells back to a standard format? (Again Format Cells
doesnt do it). *Let me know if you would like a copy of what I am
looking at exactly.

Thanks again.- Hide quoted text -

- Show quoted text -

It appears it is some how changed by a forumla I am using. *This data
is from three different time zones to begin with, I used a formula to
add the 1 or 2 hours difference I needed. *From there I paste special,
keeping only the values. *Somehow excel still thinks this data is
different.- Hide quoted text -

- Show quoted text -

hmm I am getting compile errors using "Mod" for some reason.- Hide quoted text -

- Show quoted text -

Joel, again I apperciate the Help but I am still getting duplicate
times now. -- Again if you would like to see the workbook itself
please let me know.

IE..


Original:

Column A Column B
0:00 1
0:00 1
0:00 1
0:00 1
1:15 1
1:15 1
1:45 1



After Macro:

Column A Column B
0:00 1
0:00 3
1:15 1
1:15 1
1:45 1