Help please -

[stuck4once]

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

 

[Barb Reinhardt]

Sort by location and date

Add a helper column and put this in it:
C1: COUNT
C2: =COUNTIF(A$2:A2,A2)
A2 is the first cell with data for location.

Copy this value down so that all locations are checked.

I think I'd sort next by COUNT (this is column C in my example)

D1: Concatenate
D2: =A2&"-"&C2
Copy values down

E1: 1st Date
F1: 2nd Date
G1: 3rd Date
H1: 4th date
E2: =INDEX($B:$B,MATCH($A2&"-"&LEFT(E$1,1),$D:$D,0))

Copy for all cells where the count is = 1

HTH,
Barb Reinhardt

 

[Mike H]

Hi,

Finding the first instance is a simple vlookup:-

=VLOOKUP(D1,A1:B19,2,FALSE)

Where D1 is the address to lookup and A119 is the address and date.

Finding the second and subesquent occurence is done differently using:-

=INDEX($A$2:$B$19,SMALL(IF($A$2:$B$19=$D$1,ROW($A$2:$B$19)-ROW($A$2)+1,ROW($B$19)+1),2),2)

In this formula the second to last 2 tells it to find the second occurence
of the lookup value in D1. Change this t a 3 for the 3rd etc.

It's an array so enter with ctrl+shift+enter

Mike

 

[Max]

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.