Calculate and Show the Unique Entries in a Column

Rixn,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider.

Sample raw data:




After the macro:




Then you add Green to the bottom of the list in column A:




And, run the macro again, and you get this:




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniques macro.

Start your data in A2.
For the number of Unique Items, try (adjust range to suit):
For the list of Unique Items: enter this CSE in B2 (confirm with Ctrl+Shift+Enter, don't just hit the enter key), and then copy down:
Then as you add new colors, the Sumproduct formula will increase and they'll appear in Col B

Rixn,

Here is another macro for you to consider, with a counter:




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniquesV2 macro.

Thanksboth hiker and Ron for your solutions. I try to solve as much as I can with formulas, but it is good to know that there are people out there who have the knowledge necessary to fix things in VBA if the formulas won't be enough. For this problem I used RonBs elegant solution.

I don't fully understand how it works, which annoys me, but I'm grateful for the fact it does. I won't ask for an explanation, but i could be helped in my learning by letting me know one thing in the first formula: It's the part in the end that puzzles me:

&""

Click to expand...

I can't seam to find any support for this in the help. But I think I get the idea - by using & you can add the posts in the array between the "", and it only counts those posts that match that criteria.

1) Did I get it right?

2) ..and how come it isn't explained in the help?

3) Is it explained somewhere else, as in an expert help of some sort that covers many of these useful stuff?

Here's another formula solution to consider.
Again it requires the data to start in row 2 (or below), but it is a bit shorter and does not require confirmation with Ctrl+Shift+Enter.
It also has a simpler method of getting the count by getting it after the list, not before.

Rixn,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

Thanks Peter_SSs
That as an even better solution!

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

RonB1111 said:

Peter’s “?*” in the countif criteria was new to me and when googling it I didn’t find any explanation.

Click to expand...

I'm not sure what version of Excel you are using or what you Googled as the description of wildcards with COUNTIF is in the built-in Help of at least Excel 2010 and 2007 (don't have others to check at the moment) and when I Googled "excel countif syntax" the first result was this which also explains wildcard use with the function.

RonB1111 said:

It also works as “*?” but not with just the ? or just the *. While I don’t fully understand this,..

Click to expand...

When you say it doesn't work with just ? or just * I assume you mean it doesn't give the required result in this particular problem.

Just ?
=COUNTIF(B2:B100,"?")
Since the ? wildcard is used to match a single character, this would count the cells in B2:B100 that consisted of a single character. In our case there aren't any so the formula correctly returns 0, but if you added, say, "G" to the list in column A then this COUNTIF would correctly return 1.

Just *
=COUNTIF(B2:B100,"*")
The * wildcard is used to match any sequence of characters, including none, so this formula will count every cell in B2:B100 that contains the formula since the column B formula returns a text string in each case**.

** That comment is based on column A containing text strings, not numbers. If column A could contain numerical values then a different approach would be needed to do the count.