Calculate and Show the Unique Entries in a Column

[Rixn]

I have this in column A, starting with row 1:

Blue
Yellow
Blue
Red
Red
Yellow

Question 1)
I want to calculate how many unique entries I have in that column - that is 3 (for Blue, Yellow and Red).

Question 2)
I want to keep column 2 populated with these unique entries, like this:

Blue
Yellow
Red

(in the order they occur in column A)

..and when Green is added in column A then column B should be updated with that too.

_____
(I've read several posts on my topic, but no one seam to be exact what I need, and I can't figure out how to tweak the formula to fit my needs. I've tried FREQUENCY and SUMPRODUCT but in both I've encountered problems. For the FREQUENCY example there was a TRUNC part that I couldn't bypass, and for the SUMPODUCT I never got the hang of the -- thing. I hope this can help.)

 

[Aladin Akyurek]

Row\Col	A​	B​	C​
1​	ITEM		3​
2​	**Blue		Distinct Item List
3​	**Yellow		Blue
4​	**Blue		Yellow
5​	**Red		Red
6​	**Red
7​	**Yellow

Assign Items as name to the range of A2:A7 using the Name Box.

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Items)-ROW(INDEX(Items,1,1))+1

C1, control+shift+enter (cse), not just enter:

=SUM(IF(FREQUENCY(IF(Items<>"",MATCH(Items,Items,0)),Ivec),1))

C3, cse and copied down:

=IFERROR(INDEX(Items,SMALL(IF(FREQUENCY(IF(Items<>"",
    MATCH(Items,Items,0)),Ivec),Ivec),ROWS(C$3:C3))),"")

 

[Rixn]

RonB and PeterSSs - you are much helpful, and you manage to explain these things in a very easy understandable way. Thanks for the added value!

 

[RonB1111]

Peter,
I'm using Excel 2013.
While I'm familiar with * and ? as wildcards, it never dawned upon me to combine them like you did to exclude blanks. I used excel "?*" countif as a google search & while many links came up, I only found 1 with "?*" but no explanation. It now seems obvious to me. One of the most useful feature of this forum for me is seeing and learning the techniques others use to solve problems. Many thanks.
Ron

 

[Aladin Akyurek]

Rixn,

Peter’s formula’s are much more eloquent than mine.

To answer your question on the &”” at the end of the SUMPRODUCT formula, it’s to deal with blank cells. You’ve probably already discovered that without the &”” the formula returns #DIV/0. If you break down the formula results by going into the formula bar and highlighting just the A2:A100 and pressing the F9 key, you’ll see in the array results that blank cells are considered 0, which causes the #DIV/0 error. Now hit Esc and highlight A2:A100&”” and press the F9 key and you’ll see that the blank cells are now considered blanks and there is no longer a #DIV/0 error.

You’re right that there’s very little documentation on this. I stumbled upon it some time back but I can’t find where now. I don’t remember any explanation other than it worked, so I backed into what was happening by breaking down the formula as described above. Peter’s “?*” in the countif criteria was new to me and when googling it I didn’t find any explanation. It’s a clever use of the wildcard characters which exclude cells with formulas returning blanks. It also works as “*?” but not with just the ? or just the *. While I don’t fully understand this, it’s another nice technique that I’ll keep in the back of my head – thank you again Peter.

Ron

http://www.mrexcel.com/forum/excel-questions/70835-count-distinct-function.html

 

[Aladin Akyurek]

Peter,
I'm using Excel 2013.
While I'm familiar with * and ? as wildcards, it never dawned upon me to combine them like you did to exclude blanks. I used excel "?*" countif as a google search & while many links came up, I only found 1 with "?*" but no explanation. It now seems obvious to me. One of the most useful feature of this forum for me is seeing and learning the techniques others use to solve problems. Many thanks.
Ron

I use this "?*" all the time. Does not require much explanation: It means "begins with any char" and applies to strings...

 

[Rixn]

[how do I delete a post?]