• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get list of Unique Records

J

Joris Hermans

New Member
Can someone please help me out on this.
I've been cruisin' the net, but did'nt find an example or explanation that I could understand.

I do have an sheet with a table (a single column with +- 40 rows) that the customer must fill in.
One collumn is designed for the user to make a selection of a list of +- 20 topics.

starting in another cell, I would like to give the customer an overview of UNIQUE list of items he already had selected (one cell) with the number of times this item has been selected (using "countif")
It's getting the Unique list, I'm having problems with. I know the range were to look up (J5:J50) and I know where to start showing the UNIQUE list (A1:Axxx).
I don't know how long the list will be (depends on the user's selection), and most of all, I don't know how to build that list.
In SQL this would be someting like "Select DISTINCT [ItemName] from UsersChoices_Table"

I guess there must be an Excel variant for "distinct" and supose it is an arry-type of function.
But I didn't get the hang of these kind of formulas, nor do I know the excact DISTINCT-alike formula, nor do I know (if any) the DUTCH translation.

Anyone can help me with a clear and simple example?
TIA
 
Hello Somendra
First of all, thank you very much for such a quick reply.

Unfortunaltely, there is something that I do not get to work.

I did created the name 'List' (and used the same sigma-sign in the definition as you did, althought I hav no clou what this formula means)
Then I selected 15 empty cells in which I would like to see the Unique List. I did copy/paste your formula, added the { and } brackets and pushed SHFT + CTRL + ENTER. But the result I got, was the formula-tekst (not the values) copied over the selected cells. Can you help me on this one as well?

And now when I m bussy bothering you, can I ask you an extra question? Can you give some explenation on the formula you used?

Thanks in advance
Joris
 
@Joris Hermans

The list in column J will be your list of words with duplicates. Now I had created a dynamic named formula which will take the words in your list. Now to extract unique words, copy formula in A1 and paste in only one cell your choice and press Ctrl+Shift+Enter than copy the formula down.

Regards,
 
Thanks again for the fast reply!
What I did: I replaced the word "LIST" in the array formula by "J16:J52" (the range where the duplicate words will show up)
I managed to get the array formula working .. but instead of the value, i get "0" (the value ZERO). However, I do get exact the same number of Zero's, as I get distinct records. So, it's beginning to work.

Rest my questions:
* How do I get the "ZERO" (result of the array) replaced by the actual value (the distinct words)
* Can you give a bit of explenation on the array formula (I'm quiet a newbie on excel)
* can you give a bit of explanation on the dynamic list-name (I never saw the Ω sign in a formula. Has it a special meaning?)

Maybe it makes things clear for you to look at my excel wich I included
(a little tip on how to use this excel: if you enter a "end hour", a new line will appear (with a max of four lines a day. You can disable the macro, manually make lines visible or whatever. A completly different issue is that from time to time, when eddiding a cell (not necesserely in the end-hour range), the sheet "freeze". Then you have to click the other sheet and the problem is solved.)

Please don't feel obliged to answer. I already appreciate the previous answers. But I would be lying if I told you I wasn't putting all my hope on you :)

Kindest Regards
 

Attachments

Hi See your file, I did some modifications in the formula. If this is the result you are looking for than tell me I will go ahead and explain you what the formula is doing.

Regards,
 

Attachments

You must log in or register to reply here.
Back
Top