Question: what if there are blank cells?

It makes things more complicated!

Change the formula in B1 to:

=SUMPRODUCT((A2:A25<"")/COUNTIF(A2:A25,A2:A25&""))

Change the array formula** in B2 to:

=IF(ROWS(B$2:B2)B$1,"",INDEX(A$2:A$25,SMALL(IF(A$ 2:A$25<"",IF(ROW(A$2:A$25)-ROW(A$2)+1=MATCH(A$2:A$25,A$2:A$25,0),ROW(A$2:A$25 ))),ROWS(B$2:B2))-ROW(A$2)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Jim" wrote in message
...
This looks great, and I'm sure I'm close to getting it to work.

Question: what if there are blank cells?

Jim

"T. Valko" wrote:

Try this...

Data in the range A2:A25 (assuming no empty cells within the range).

Enter this formula in B1. This will return the count of uniques.

=SUMPRODUCT(1/COUNTIF(A2:A25,A2:A25))

Enter this array formula** in B2 and copy down until you get blanks:

=IF(ROWS(B$2:B2)B$1,"",INDEX(A$2:A$25,SMALL(IF(RO W(A$2:$A$25)-ROW(B$2)+1=MATCH(A$2:A$25,A$2:A$25,0),ROW(A$2:A$25 )),ROWS(B$2:B2))-ROW(B$2)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Jim" wrote in message
...
Hello,

Because of the returns I'm looking for in this worksheet I cannot use
filters or pivot tables. Also, I'm sorry to post again but I really
need
help here and the only I answer I received before was for filters.
Formulas
are really needed.

I would like to ask for help with the following: In column A I have
repetative data. It's the name of my sales people. It looks like
this:

Denise
Jim
Jeff
Anthony
Beth
Brian
Judy
Eric
Bonnie
Jim
Jeff
Summer
Cynthia
Brian
Brenda
Ryan
Kay
Jeff
Beth
Jim
Eric
Judy
Bonnie
Kay

I need a formula that I can copy into Column B that extract their name,
and
show it in column B only once.

So it will look like this:
Anthony
Beth
Bonnie
Brenda
Brian
Cynthia
Denise
Eric
Jeff
Jim
Judy
Kay
Ryan
Summer

Can anyone help me with this
And thank you.

Jim