Reporting Problem

[Patrick]

Hi all,

I have a challenge for you...

Below is taken from an excel spreadsheet, if you copy it and paste it
into excel it should make a bit of sense:

Abertillery JC Ph01b IMP1239Ph01b 05/12/2005
Abertillery JC Ph02a IMP1239Ph02a 21/12/2005
Abertillery JC Ph03a IMP1239Ph03a 13/01/2006
Abertillery JC Ph04a IMP1239Ph04a 21/12/2005
Aberystwyth JC (Cab)Ph03a IMP1555Ph03a(Cab) 03/02/2006
Aberystwyth JC (Cab)Ph04a IMP1555Ph04a(Cab) 03/02/2006
Aberystwyth JC (Cab)Ph02ab IMP1555Ph02ab(Cab) 21/12/2005
Aberystwyth JC Ph01b IMP1555Ph01b 11/11/2005
Aberystwyth JC Ph02c IMP1555Ph02c 06/01/2006
Aberystwyth JC Ph03b IMP1555Ph03b 10/02/2006
Aberystwyth JC Ph03e IMP1555Ph03e 10/02/2006
Aberystwyth JC Ph04c IMP1555Ph04c 10/03/2006
Aberystwyth JC Ph04e IMP1555Ph04e 10/03/2006
Airdrie JC Ph01c IMP1132Ph01c 23/09/2005
Airdrie JC Ph02a IMP1132Ph02a 04/11/2005
Airdrie JC Ph02c IMP1132Ph02c 27/01/2006
Airdrie JC Ph1b ( Cab ) IMP1132Ph1b( Cab ) 04/11/2005

The above is just a sample of a spreadsheet that I need to produce a
report from. I need to find the latest date, for each site.

For example, in the above sample, there are 3 sites, Abertillery JC,
Aberystwyth JC, and Airdrie JC. So, the report should produce the
following:

Abertillery JC - 13/01/2006
Aberystwyth JC - 10/03/2006
Airdrie JC - 27/01/2006

But I dont know how to get that report. I am thinking that the second
column, the first 7 characters will be the same for each site, so that
will identify the groups of sites, from which to find the latest date,
but I'm not sure how to go through and get the latest date from each,
remembering that there is a lot more data than the above sample.

Any ideas?

 

[Bob Phillips]

Add these formula

I1: =A1
I2: =IF(ISERROR(MATCH(0,COUNTIF(I$1:I1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(I$1:I1,$A$1:$A$2
0&""),0)))

the second formula is an array formula, so commit with Ctrl-Shift-Enter, and
copy down to max possible

J1: =MAX(IF(A1:A20=I1,D120))

which is also an array formula, and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Make that

=IF(I1<>"",MAX(IF(A1:A20=I1,D120)),"")

in J1.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Patrick]

cool thanks, that did the trick.