Okay, try it this way

A1: = Sheet1!A1
A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!A$1:A$20 &""),0)),"",
INDEX(IF(ISBLANK(Sheet1!A$1:A$20),"",Sheet1!A$1:A$ 20),MATCH(0,COUNTIF(A$1:A1
,Sheet1!A$1:A$20&""),0)))

Copy A1:A2 acros to C1:C2

Then copy A2:C2 down as far as required.

As before A2 is an array formula, so do that before copying

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Becks" wrote in message
...
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
"Bob Phillips" wrote:

Becks,

Does the same name have the same ref? If so no problem, if not do we
uniquify on name, or name and ref? Then do you want latest date or
earliest
date if we still get dups?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Becks" wrote in message
...
Thanks Bob, this works fine for the ref number and name, but people do
have
the same start date, any ideas?

"Bob Phillips" wrote:

On the second sheet

A1: = Sheet1!A1
A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"",


INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$
1:A1,Sheet1!$A$1:$A$20&""),0)))

which is an array formula, it should be comnmitted with
Ctrl-Shift-Enter,
not just Enter. Copy A2 down to some point beyond the last item (to
cater
for extras).

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Becks" wrote in message
...
I have a spreadsheet, and on the first sheet i have various client
data.
To
avoid having to re-enter some of the data on sheet two, I have
entered
formulas to pull it through from sheet 1. It is pulling through,
ref
number,
name, and a start date. It is likely that some people could
appear
more
than
once, but I only want them to show once on sheet 2. I have tried
to
use
advanced filter, but it is looking at the formula rather than the
result,
so
it is not filtering out the repeated data. Is there anything i
can do
to
get
round this, I have to get it finished for next week and i am
starting
to
panic! Please help!

Thanks

Becks