On Fri, 20 Jan 2006 09:17:05 -0800, "Becks"
wrote:

Hi Bob - The data is set up as follows:

ColA ColB ColC
Ref No Name Start Date
123 Bloggs 01/04/05
123 Bloggs 18/12/05
321 Jones 01/04/05

The ref number for each person is always the same. So going off the example,
i just want to show rows 1 & 3, as row 2 is a duplicate. I entered the
formula in Col A, and copied it down, I changed the Col ref in the formula
and did the same in Col B and it worked but when i tried it in the date
column it didn't work. I'ts probably just me doing something wrong, hopefully
this explains it better.

Thanks for your time.

Becks

Here's another way:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/forums

Assumption: Your data is entered on Sheet1! with labels in row 1 and data in
A2:C100

Sheet2!A2: =INDEX(UNIQUEVALUES(Sheet1!$A$2:$A$100,1),ROWS($1: 1))
Sheet2!B2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$100,2,FAL SE))
Sheet2!C2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$100,3,FAL SE))

Select A2:C2 and copy/drag down as far as needed.


--ron