Identifying the first date(s) from a list
 

I pull data from another application. In Column A it returns a list list of
times stampes by agent actvites in a "2/20/2006 4:38:37 PM" format. For
every event of the day that something changes the data will stamp the event
in another cell in that same column. What I want to be able to do is to
identify the first activity of each day in this list. There can be easily
several days (making hundreads/thousands of rows) worth of information and it
easy sort them column in order but I want to quickly find the first event of
each day. Preferably pulling the first event of each new day onto antoher
spreadsheet or area in the workbook.

I hope this makes since?
The list looks something like this
Column A
1 2/20/06 7:59 AM
2 2/20/06 8:00 AM
3 2/20/06 8:34 AM
4 2/20/06 8:34 AM
5 2/20/06 8:34 AM
6 2/20/06 9:05 AM
7 2/21/06 8:02 AM
8 2/21/06 8:47 AM
9 2/21/06 9:29 AM
10 2/21/06 9:29 AM
11 2/21/06 9:29 AM
12 2/21/06 9:33 AM
13 2/21/06 9:34 AM
14 2/21/06 9:42 AM
15 2/22/06 8:02 AM
16 2/22/06 8:02 AM
17 2/22/06 8:02 AM
18 2/22/06 8:18 AM
19 2/22/06 8:19 AM
20 2/22/06 8:32 AM
21 2/22/06 8:34 AM

What I want to see is below
2/20/06 7:59 AM
2/21/06 8:02 AM
2/22/06 8:02 AM

Identifying the first date(s) from a list
 

How about conditional formatting.

Select all the cells in column A
Menu FormatConditional Formatting
Change Condition 1 To Formula Is
Add a formula of =A1=MIN(IF(INT($A$1:$A$100)=INT(A1),$A$1:$A$100))
Click Format
Select the pattern tab
Choose a colour
Exit



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"amy" wrote in message
...
I pull data from another application. In Column A it returns a list list
of
times stampes by agent actvites in a "2/20/2006 4:38:37 PM" format. For
every event of the day that something changes the data will stamp the
event
in another cell in that same column. What I want to be able to do is to
identify the first activity of each day in this list. There can be easily
several days (making hundreads/thousands of rows) worth of information and
it
easy sort them column in order but I want to quickly find the first event
of
each day. Preferably pulling the first event of each new day onto antoher
spreadsheet or area in the workbook.

I hope this makes since?
The list looks something like this
Column A
1 2/20/06 7:59 AM
2 2/20/06 8:00 AM
3 2/20/06 8:34 AM
4 2/20/06 8:34 AM
5 2/20/06 8:34 AM
6 2/20/06 9:05 AM
7 2/21/06 8:02 AM
8 2/21/06 8:47 AM
9 2/21/06 9:29 AM
10 2/21/06 9:29 AM
11 2/21/06 9:29 AM
12 2/21/06 9:33 AM
13 2/21/06 9:34 AM
14 2/21/06 9:42 AM
15 2/22/06 8:02 AM
16 2/22/06 8:02 AM
17 2/22/06 8:02 AM
18 2/22/06 8:18 AM
19 2/22/06 8:19 AM
20 2/22/06 8:32 AM
21 2/22/06 8:34 AM

What I want to see is below
2/20/06 7:59 AM
2/21/06 8:02 AM
2/22/06 8:02 AM

Identifying the first date(s) from a list
 

This is a little closer, but it identifies by identifying the lowest cell.
But what happens in my data is that I have several days in the same listing
that I need to find the first entry for each day in that list

"Bob Phillips" wrote:

How about conditional formatting.

Select all the cells in column A
Menu FormatConditional Formatting
Change Condition 1 To Formula Is
Add a formula of =A1=MIN(IF(INT($A$1:$A$100)=INT(A1),$A$1:$A$100))
Click Format
Select the pattern tab
Choose a colour
Exit



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"amy" wrote in message
...
I pull data from another application. In Column A it returns a list list
of
times stampes by agent actvites in a "2/20/2006 4:38:37 PM" format. For
every event of the day that something changes the data will stamp the
event
in another cell in that same column. What I want to be able to do is to
identify the first activity of each day in this list. There can be easily
several days (making hundreads/thousands of rows) worth of information and
it
easy sort them column in order but I want to quickly find the first event
of
each day. Preferably pulling the first event of each new day onto antoher
spreadsheet or area in the workbook.

I hope this makes since?
The list looks something like this
Column A
1 2/20/06 7:59 AM
2 2/20/06 8:00 AM
3 2/20/06 8:34 AM
4 2/20/06 8:34 AM
5 2/20/06 8:34 AM
6 2/20/06 9:05 AM
7 2/21/06 8:02 AM
8 2/21/06 8:47 AM
9 2/21/06 9:29 AM
10 2/21/06 9:29 AM
11 2/21/06 9:29 AM
12 2/21/06 9:33 AM
13 2/21/06 9:34 AM
14 2/21/06 9:42 AM
15 2/22/06 8:02 AM
16 2/22/06 8:02 AM
17 2/22/06 8:02 AM
18 2/22/06 8:18 AM
19 2/22/06 8:19 AM
20 2/22/06 8:32 AM
21 2/22/06 8:34 AM

What I want to see is below
2/20/06 7:59 AM
2/21/06 8:02 AM
2/22/06 8:02 AM

Identifying the first date(s) from a list
 

Try this: Assumes data is in column A starting row 2 and is sorted in
ascending date/time sequence

Sub FindFirstDate()

Dim r As Long, rr As Long, lastrow As Long
Dim n As Integer
Dim rnga As Range

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

With ws1

lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
r = 2
rr = 2
nextrow = r
FirstDate = Format(.Cells(r, "A"), "DD/MM/YYYY")

For r = 3 To lastrow
If Format(.Cells(r, "A"), "DD/MM/YYYY") < Format(.Cells(r - 1,
"A"), "DD/MM/YYYY") Then
ws2.Cells(rr, "A") = .Cells(nextrow, "A")
rr = rr + 1
FirstDate = Format(.Cells(r, "A"), "DD/MM/YYYY")
nextrow = r
End If
Next r
ws2.Cells(rr, "A") = .Cells(nextrow, "A")
End With

End Sub

"amy" wrote:

This is a little closer, but it identifies by identifying the lowest cell.
But what happens in my data is that I have several days in the same listing
that I need to find the first entry for each day in that list

"Bob Phillips" wrote:

How about conditional formatting.

Select all the cells in column A
Menu FormatConditional Formatting
Change Condition 1 To Formula Is
Add a formula of =A1=MIN(IF(INT($A$1:$A$100)=INT(A1),$A$1:$A$100))
Click Format
Select the pattern tab
Choose a colour
Exit



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"amy" wrote in message
...
I pull data from another application. In Column A it returns a list list
of
times stampes by agent actvites in a "2/20/2006 4:38:37 PM" format. For
every event of the day that something changes the data will stamp the
event
in another cell in that same column. What I want to be able to do is to
identify the first activity of each day in this list. There can be easily
several days (making hundreads/thousands of rows) worth of information and
it
easy sort them column in order but I want to quickly find the first event
of
each day. Preferably pulling the first event of each new day onto antoher
spreadsheet or area in the workbook.

I hope this makes since?
The list looks something like this
Column A
1 2/20/06 7:59 AM
2 2/20/06 8:00 AM
3 2/20/06 8:34 AM
4 2/20/06 8:34 AM
5 2/20/06 8:34 AM
6 2/20/06 9:05 AM
7 2/21/06 8:02 AM
8 2/21/06 8:47 AM
9 2/21/06 9:29 AM
10 2/21/06 9:29 AM
11 2/21/06 9:29 AM
12 2/21/06 9:33 AM
13 2/21/06 9:34 AM
14 2/21/06 9:42 AM
15 2/22/06 8:02 AM
16 2/22/06 8:02 AM
17 2/22/06 8:02 AM
18 2/22/06 8:18 AM
19 2/22/06 8:19 AM
20 2/22/06 8:32 AM
21 2/22/06 8:34 AM

What I want to see is below
2/20/06 7:59 AM
2/21/06 8:02 AM
2/22/06 8:02 AM