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

List unique values

joshallen78

New Member
I have a column of information that contains a list of information, sometimes the same info will be repeated and sometimes it's only in once.


I'm trying to make a formula that when drug down will list each unique piece of information one time so I can do a sumif on it in the columns to the right. It is similar to a pivot table but a bit different, so that is why I'm trying to use a formula.


Any help would be much appreciated.
 
Hi,

Welcome to the forum.


I would encourage you to try the Custom Google Search located on the top right side of this window, using a search string such as "unique values". I think you will find what you are looking for (and more).


Cheers,

Sajan.
 
=INDEX(A:A,MATCH(0,COUNTIF($B$1:B1,A:A),0))


use wiith Ctrl + Shift + Enter


Assuming your data is in A column, you will type the formula in B2.....[B1 would be blank]..
 
This is awesome! Thanks Vasim. The only issues is if I have 50 cells, how do I force all of the array formulas to compute at the same time. So far I have to individually click each one of them to force calculation.
 
Last edited:
@Ankur Ojha
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, usually uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. But in this case the answer is: copy down B2 as required (i.e., to the last row used in A column).

Regards!

PS: Please don't answer here at this thread.
 
Dear joshallen78
Think your set of data is like below (A1:A12). in the set of data 1004 and 1005 are repeated.
ProdCode
1001
1003
1004
1005
1006
1004
1009
1005
1012
1004
For listing out unique records from the above data, do the following steps.
1. Select the data A1:A12
2. Data tab>Sort & Filter group>Advanced
3. Select the option "Copy to another location
4. Click in copy to
5. Check in "Unique records only"
6. Now specify where you want to show unique records (assume you want to show the unique records from F2)
7. Click OK
Done!

Regards,
Nazmul Muneer
 
Hi, nazmul_muzneer!
Considering the topic and last post dates previous to Ankur Ojha's post, it's hardly probable that the OP comes back to this thread to check new comments.
Regards!
PS: Please don't answer here at this thread.
 
Back
Top