Show results once from a column

J

Jim

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
 
T

T. Valko

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(ROW(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.
 
J

Jim

This looks great, and I'm sure I'm close to getting it to work.

Question: what if there are blank cells?

Jim
 
T

T. Valko

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.
 
Top