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

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

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

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

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

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

It works fine in Columns A & B, but with the dates in Column C, its showing
the same date in every cell I copy it down to?

"Bob Phillips" wrote:

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

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

Absolutely fantastic!!!! Thanx very much! Just what i was looking for.

Cheers

Becks

"Ron Rosenfeld" wrote:

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

On Wed, 25 Jan 2006 02:50:02 -0800, "Becks"
wrote:

Absolutely fantastic!!!! Thanx very much! Just what i was looking for.

Cheers

Becks


Glad to help. Thanks for the feedback.


--ron