Hi,

Finding the first instance is a simple vlookup:-

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

Where D1 is the address to lookup and A1:D19 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

"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