Another thought using non-array formulas
is illustrated in this sample construct:
http://www.savefile.com/files/739844
Properties n dates serviced.xls
Source data assumed in sheet: X, cols A and B from row2 down, addresses in
col A, dates of service in col B (assumed to be real dates)
In X,
List the addresses' key text in D1 across, eg: 1 Abbey, 1 Arrow Place, etc
Put in D2:
IF($B2="","",IF(ISNUMBER(SEARCH(D$1,$A2)),$B2+ROW( )/10^10,""))
Copy D2 across as far as required, then fill down to cover the max expected
extent of source data
In another sheet: Y (say),
In A2:
=INDEX(X!$D$1:$IV$1,ROW(A1))
Copy A2 down to extract the list of the key addresses placed in X's D1 across
Then place in B2:
=IF(COLUMN(A1)COUNT(OFFSET(X!$C:$C,,ROW(A1))),"", INDEX(X!$B:$B,MATCH(SMALL(OFFSET(X!$C:$C,,ROW(A1)) ,COLUMN(A1)),OFFSET(X!$C:$C,,ROW(A1)),0)))
Format B2 as date, copy B2 across to say, K2, fill down as far as required.
Cols B to K will return the dates of service as desired for the addresses in
col A, all neatly bunched to the left. Dates will be extracted in chronologic
sequence from left to right, with earliest dates leftmost.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"stuck4once" wrote:
Hi, I am wondering if anyone can help me.
I have a list of address and a list of dates that a service was carried out
at those addresses:
A
B
1 Abbey Terrace, Newport Pagnell, Newport Pagnell 14-Apr-05
1 Abbey Terrace, Newport Pagnell, Newport Pagnell 08-Feb-07
1 Arrow Place, Bletchley, Milton Keynes 10-Jan-07
1 Almond Close, Newport Pagnell, Newport Pagnell 09-Nov-05
1 Abbey Terrace, Newport Pagnell, Newport Pagnell, 01-Feb-06
1 Althorpe Crescent, Bradville, Milton Keynes 04-Nov-05
1 Almond Close, Newport Pagnell, Newport Pagnell 22-Nov-06
1 Althorpe Crescent, Bradville, Milton Keynes 05-Oct-06
1 Arrow Place, Bletchley, Milton Keynes 03-May-05
What i need is to be able to summarise as follows:
Address 1st date 2nd date 3rd date 4th
date
1 Abbey 15-04-05 01-02-06 08-02-07
if applicable.
I would go through and do this manually but there were 28,000 services
carried out on 12,000 properties.
Any help on this will be greatly appreciated.
Many thanks