Display unique data only

B

Becks

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
 
B

Bob Phillips

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)
 
B

Becks

Thanks Bob, this works fine for the ref number and name, but people do have
the same start date, any ideas?
 
B

Bob Phillips

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)
 
B

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
 
B

Bob Phillips

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)
 
B

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?
 
R

Ron Rosenfeld

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
Click to expand...

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,FALSE))
Sheet2!C2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$100,3,FALSE))

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


--ron
 
Top