Distinct list formula

[summergs]

Does anyone know of a formula I could use to compile a distinct list o
entries from another column. I know this could be done with th
advanced filter, and I know it could be done with VBA, but I need to d
it with a formula. Likewise, I can't use a pivot table because then th
data could be changed. I have to pull a distinct list and thei
correponding values from some static data so it can be graphed.
Thank

 

[Guest]

When you say distinct list of entries I'm assuming you mean unique records.

One way to do this would be with a helper column. If the data which
distinguishes one row from another is in column A, then the helper formula
would be =IF(A1=A2,1,"") assuming the data table is sorted by column A.

If you provide more information about the data you're trying to sort I could
make this explanation more concrete.

 

[Bob Phillips]

B1: =A1

B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

copy B2 down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

See Chip Pearson's site topic "Extracting Unique Entries"

 

[summergs]

Sorry, yes I'm looking for Unique entries I was saying distinct as in
SQL "select distinct...". Here's a mock up of what I'm dealing with.
I dump data into a worksheet and then slice and dice it lots of
different ways, but I have static information such as
Apples
Oranges
Grapes
Melon
Apples
Grapes
Cherries
Apples

So I want to change this into:
Apples
Oranges
Grapes
Melon
Cherries

I quickly tried Bob's array formula and it seemed to work, though if
the data in column A was seen as a dupe column B was blank, leaving me
with gaps. I can probably clean this up by combining the info into
column C but i'll get to that. I'm just looking for other ways to
accomplish this. Also, if someone knows of an article please include
the link. I'm been searching for the article suggested by Evan but the
search functionality on the website leaves a little to be desired.
Thanks again.

 

[Biff]

Bob's formula works just fine for me. The only time it leaves a blank is if
all the uniques have been extracted (these blanks are at the end of the
range by design) OR....if there is/are empty cells within the original list.

Biff