Find first and last dates a reort was printed.

D

dids72

Hello,

I have a wookbook that contains about 180,000 entries/rows in it. The
data is in column A and B. In Column A I have Mainframe Report
Number's and in Column B are the dates they have printed. The Report
number repeats on each row for the different dates in Column B as shown
below.

Column A Column B
AA000001 6/23/06
AA000001 1/23/06
AA000001 5/15/04
AA000002 3/01/06
AA000002 2/02/05
AA000002 1/15/04
AA000002 12/18/03

Is it possible to write a macro to show the 1st and last time a report
has printed. The data would then look like below.

Column A Column B
AA000001 6/23/06
AA000001 5/15/04
AA000002 3/01/06
AA000002 12/18/03

Any help would be greatly appreciated.

Thanks,

DIDS
 
D

Don Guillett

This is an array formula which must be entered/edited using ctrl+shift+enter

=MIN(IF($A$14:$A$20=A14,$B$14:$B$20))
 
B

Biff

Hi!

It depends on how many different report numbers you have as to whether this
is really a good solution.

I tried it on a file with 3 sheets. In each sheet I put in random dates in
the range A1:A60000. In B1:B60000 I put in random numbers from 1 to 100
representing the report numbers.

I wanted to see how long it would take to calculate this array formula:

=MIN(MIN(IF(B1:B60000=95,A1:A60000)),MIN(IF(Sheet2!B1:B60000=95,Sheet2!A1:A60000)),MIN(IF(Sheet3!B1:B60000=95,Sheet3!A1:A60000)))

95 = report number

Surprisingly, calc time was about 1 second. Of course, this was only for one
report number.

If there were 100's or 1000's of different report numbers..........eh!

Biff
 
D

dids72

Thanks for the replies. I tried your suggestions but they are only
displaying a number. I am trying to be able to view the data for the
first time and last time each report has been printed. So the below
first set of data would return the results of the data shown belw it. I
greatly appreciate the help.

Original Data AS it Display's Now
Column A Column B
AA000001 6/23/06
AA000001 1/23/06
AA000001 5/15/04
AA000002 3/01/06
AA000002 2/02/05
AA000002 1/15/04
AA000002 12/18/03


Results Desired After Macro
Column A Column B
AA000001 6/23/06
AA000001 5/15/04
AA000002 3/01/06
AA000002 12/18/03

Thanks,

DIDS


P.S. - Sorry about the multipe post's of the original topic. Each time
I typed my topic and clicked the submit button. I received a page with
the following message "Oops, we can't open the web page you requested
....- Error Unknown - Unknown error with URL". I thought that my post's
didn't make it through but obviously they did. Sorry again.
 
B

Biff

I tried your suggestions but they are only displaying a number

Format the cell(s) as DATE. That's what DATES are, formatted numbers!

For the MAX date just use the same formula and change MIN to MAX.
Results Desired After Macro
Column A Column B
AA000001 6/23/06
AA000001 5/15/04
AA000002 3/01/06
AA000002 12/18/03
Click to expand...


A better layout would be:

..........A.................B.................C
AA000001.......6/23/06........5/15/04
AA000002 ......3/01/06........12/18/03


Column B would use the MAX formula and column C would use the MIN formula:

Biff
 
D

dids72

Hello,

Thanks again for your help. I have formatted the column I placed the
array in as DATE. However, it is just displaying the earliest date for
a range and not filtering out the rest of the data. I am trying to
have it take the data on the spreadsheet and only display the earliest
and latest date for each report.

Thanks,

DIDS

Original Data AS it Display's Now
Column A Column B
AA000001 6/23/06
AA000001 1/23/06
AA000001 5/15/04
AA000002 3/01/06
AA000002 2/02/05
AA000002 1/15/04
AA000002 12/18/03


Results Desired After Macro
Column A Column B
AA000001 6/23/06
AA000001 5/15/04
AA000002 3/01/06
AA000002 12/18/03
 
D

Don Guillett

The formulas given will do that. Did you try mine? did you enter using CSE?
did you format as a date?
 
D

dids72

Hi Don,

Yes I did try yours and am using CTRL+SHIFT+ENTER after inputing the
values. This is the process I am doing.

1. I formatted Column's B and C as DATE.

2. I inputed "=MIN(IF($A$2:$A$250=A14,$B$2:$B$250))" in the input box
for Cell C3 and pressed CTRL+SHIFT+ENTER.

3. It returned 6/30/2004 as a result.

I have pasted below the actual data I applied this to and the result
that is returned. Thanks for your help.

Column A Column B Column C
ReportNumber ReportDate
AC004261 3/31/2005 6/30/2004
AC004261 3/31/2005
AC004261 2/28/2005
AC004261 2/28/2005
AC004261 1/31/2005
AC004261 1/31/2005
AC004261 12/31/2004
AC004261 12/31/2004
AC004261 11/30/2004
AC004261 11/30/2004
AC004261 10/29/2004
AC004261 10/29/2004
AC004261 9/30/2004
AC004261 9/30/2004
AC004261 9/1/2004
AC004261 9/1/2004
AC004261 9/1/2004
AC004261 8/31/2004
AC004261 7/30/2004
AC004261 7/30/2004
AC004261 6/30/2004
AC004261 6/30/2004
AC004269 3/31/2005
AC004269 3/31/2005
AC004269 2/28/2005
AC004269 2/28/2005
AC004269 1/31/2005
AC004269 1/31/2005
AC004269 12/31/2004
AC004269 12/31/2004
AC004269 11/30/2004
AC004269 11/30/2004
AC004269 10/29/2004
AC004269 10/29/2004
AC004269 9/30/2004
AC004269 9/30/2004
AC004269 9/1/2004
AC004269 9/1/2004
AC004269 9/1/2004
AC004269 8/31/2004
AC004269 7/30/2004
AC004269 7/30/2004
AC004269 6/30/2004
AC004269 6/30/2004
AC004270 3/31/2005
AC004270 3/31/2005
AC004270 3/31/2005
AC004270 3/31/2005
AC004270 3/31/2005
AC004270 3/31/2005
AC004270 3/31/2005
AC004270 3/31/2005
AC004270 2/28/2005
AC004270 2/28/2005
AC004270 2/28/2005
AC004270 2/28/2005
AC004270 2/28/2005
AC004270 2/28/2005
AC004270 2/28/2005
AC004270 2/28/2005
AC004270 1/31/2005
AC004270 1/31/2005
AC004270 1/31/2005
AC004270 1/31/2005
AC004270 1/31/2005
AC004270 1/31/2005
AC004270 1/31/2005
AC004270 1/31/2005
AC004270 12/31/2004
AC004270 12/31/2004
AC004270 12/31/2004
AC004270 12/31/2004
AC004270 12/31/2004
AC004270 12/31/2004
AC004270 12/31/2004
AC004270 12/31/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 11/30/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 10/29/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 9/30/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 8/31/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 7/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 6/30/2004
AC004270 5/28/2004
AC004270 5/28/2004
AC004270 5/28/2004
AC004270 5/28/2004
AC004279 3/31/2005
AC004279 3/31/2005
AC004279 2/28/2005
AC004279 2/28/2005
AC004279 1/31/2005
AC004279 1/31/2005
AC004279 12/31/2004
AC004279 12/31/2004
AC004279 11/30/2004
AC004279 11/30/2004
AC004279 10/29/2004
AC004279 10/29/2004
AC004279 9/30/2004
AC004279 9/30/2004
AC004279 9/1/2004
AC004279 8/31/2004
AC004279 7/30/2004
AC004279 7/30/2004
AC004279 6/30/2004
AC004279 6/30/2004
AC004460 4/22/2005
AC004460 4/22/2005
AC004460 4/15/2005
AC004460 4/15/2005
AC004460 4/8/2005
AC004460 4/8/2005
AC004460 4/1/2005
AC004460 4/1/2005
AC004460 3/31/2005
AC004460 3/31/2005
AC004460 3/25/2005
AC004460 3/25/2005
AC004460 3/18/2005
AC004460 3/18/2005
AC004460 3/11/2005
AC004460 3/11/2005
AC004460 3/4/2005
AC004460 3/4/2005
AC004460 2/28/2005
AC004460 2/28/2005
AC004460 2/25/2005
AC004460 2/25/2005
AC004460 2/18/2005
AC004460 2/18/2005
AC004460 2/11/2005
AC004460 2/11/2005
AC004460 2/4/2005
AC004460 2/4/2005
AC004460 1/31/2005
AC004460 1/31/2005
AC004460 1/28/2005
AC004460 1/28/2005
AC004460 1/21/2005
AC004460 1/21/2005
AC004460 1/14/2005
AC004460 1/14/2005
AC004460 1/7/2005
AC004460 1/7/2005
AC004460 12/31/2004
AC004460 12/31/2004
AC004460 12/23/2004
AC004460 12/23/2004
AC004460 12/17/2004
AC004460 12/17/2004
AC004460 12/10/2004
AC004460 12/10/2004
AC004460 12/3/2004
AC004460 12/3/2004
AC004460 11/30/2004
AC004460 11/30/2004
AC004460 11/26/2004
AC004460 11/26/2004
AC004460 11/19/2004
AC004460 11/19/2004
AC004460 11/12/2004
AC004460 11/12/2004
AC004460 11/5/2004
AC004460 11/5/2004
AC004460 10/29/2004
AC004460 10/29/2004
AC004460 10/22/2004
AC004460 10/22/2004
AC004460 10/15/2004
AC004460 10/15/2004
AC004460 10/8/2004
 
D

dids72

FYI - There is approx. 6,400 unique report numbers out of the 180,000
entries.

Thanks,

DIDS
 
D

dids72geocaching

Any idea's on how to get a macro to give the information I am looking
for since it is not showing the results I am looking for currently?

Thanks,

DIDS
 
D

dids72geocaching

Any idea's on how to get a macro to give the information I am looking
for since it is not showing the results I am looking for currently?

Thanks,

DIDS
 
D

dids72geocaching

Any idea's on how to get a macro to give the information I am looking
for since it is not showing the results I am looking for currently?

Thanks,

DIDS
 
D

dids72geocaching

Any idea's on how to get a macro to give the information I am looking
for since it is not showing the results I am looking for currently?

Thanks,

DIDS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find the first and last occurance of a report printed. 2
Combining functions 2
Arranging dates in a single column 5
Find then highlight in yellow 6
Sum value between two dates and copy to new cell 6
Suming Up Time over the course of days 1
Modifying a complex date formual to count months 4
Two Problems 7

Top