Show results once from a column

  • Thread starter Thread starter Jim
  • Start date Start date
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
 
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.
 
This looks great, and I'm sure I'm close to getting it to work.

Question: what if there are blank cells?

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