Unique value from a column

[hoongz]

Hi, was wondering if it is possible to retrieve unique data from a whole column of data. The situation is that I will have a column of data with unknown number of records as it always changes after each use. In this column, there will be a number of unique datas each appearing a different number of times. How can I retrieve the value of each of unique data to work with. Just to add on, the number of unique data will be different each time. And each data might be mixed around. For example Mary appear in the first 2 cells followed by John in the next 3 cells and Jim in the next 1 cell. Following which Mary will appear again. Well to sum it up I'm trying to get unique data to work with which is all around the place and appearing a random number of times. Appreciate if anyone is able to help.
Thank you

P.S. The reason I'm trying to get the unique data is that this project is to write a batch file which moves files to a folder and each name represent a folder. But each of this folder requires a document which only needs to be there once. So I'm trying to get the unique data so I can get it to move the document into that folder.

[Fotis1991]

Hi

With your Names, starting in A2, try this and copy down in B2.

=IF(COUNTIF($A2:$A$100,A2)=1,A2,"")

Is this, works for you?

[Pete_UK]

Check out this link:

http://www.get-digital-help.com/2009...om-one-column/

It shows you exactly how to do that.

Hope this helps.

Pete

[dilipandey]

Hi hoongz,

Two quick options->

1) Data - Advanced Filter -> check "unique records only" and then you can copy the unique entries

2) Select that column and create a pivot (alt + D + P) and in design of pivot, move data to row and that's it.. you can get the unique entries form the only column of pivot structure.

Regards,
DILIPandey

<click on below 'star' if this helps>

[hoongz]

Hi guys. A very big thank you for your prompt response. I guess I should be adding the point that I already have a vb script running in the back doing other functions on a click of a button. This request is just a additional task I need to add in. What I'm trying to do is in the script, I have to get the unique value into a variable and do something to it before moving on to the next unique value. All without any additional effort after the user fill in the basic data like name column and file ids and clicking the button. Currently the file is able to write a batch file to move each of the file into each sub folder. A clearer picture would be as follows:
Name(Main Folder)>Invoice(Sub Folder)>some supporting documents for that invoice.
Now Im required to add a few documents into the main folder which accounts for all the invoices done in the month by the same person. Since my script writes the file based on the invoice number, the user name tends to appear multiple times. But it's not fesible to write a file to copy the same file over and over again into the same folder. Hence I'm seeking professional help in this forum to retrieve unique values under the name folder. Hope that makes my request clearer. Im sorry Im unable to show you the file I've done up so far as it contains confidential data.

[martindwilson]

In this column, there will be a number of unique datas each appearing a different number of times

doesn't that contradict itself?

[hoongz]

doesn't that contradict itself?

How does it contradict itself. It simply means that there are many different values each appearing different number of times throughout the whole column.

[martindwilson]

you cant have something unique repeating itself, it is then not unique

[hoongz]

Maybe our understanding of unique is different. My undestanding of unique is that it is a value that is different from any other possible value within the whole range that is different. But that doesn't mean it could only appear once. It's just unique from other possible different value.

[zbor]

can you upload example workbook?

I think you can use Pivot table, but it would be easier if we could see example.

[rkey]

Is this what you are looking for?

Create Unique Values from 1 column with IsInArray.xlsm

[hoongz]

I've attached a sample workbook. Basically it shows that there are many different lines from the same department. But I only need to get one of each. I've tried to use collection but it doesn't filter duplicate values. Appreciate if I could get help on this.
Thank you

[rkey]

Have you checked my file?