How do I get the unique values from a column in excel?

[Durgesh M]

I have numbers in one column (Position Control Numner) of sheet A(1000 rows),
I want in another sheet (Sheet B) the unique numbers from the column
(Position Control Numner). Plesae guide me how to achieve this functionality.
I tried advanced filed with unique values option but the output is limited to
the same sheet sheet A.

 

[Arvi Laanemets]

Hi

Create a pivot table based on this column, which counts occurrences for
every value. You get a 2-column result table, where leftmost column is one
you are searching for.
The another way is to use an ODBC query to get all distinct values from
column.

Arvi Laanemets

 

[Mike H]

Hi,

Name the range you want the unique values from by selecting it then
Insert|Name|define and call it say MyRange
Click Ok

Go to the sheet where you want the data displayed and then
Data|Filter|Advanced filter
In the 'Listrange' box enter =MyRange
Check 'Copy to another Location'
Select 'Unique records only
Pick a cell where the data are to go.

In summary you are correct Excell cannot push advanced filtered data to
another sheet but it can pull it.

Mike

 

[Ashish Mathur]

Hi,

I think it can be achieved without naming.

1. On the other sheet, go to Data > Filter > Advanced Filter (say you are
in cell A3);
2. Select "Copy to another location";
3. In the list range box, select the range which is in the other worksheet;
4. Leave Criteria blank;
5. In the Copy to box, select the cell on which you currently are I.e. what
you will see is Sheet2!A3
6. Check the box for Unique records

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Durgesh M]

Thanks Mike, this has solved my problem. Thanks again.

Regards,

Durgesh.

 

[Durgesh M]

Mike,

I tried this and it worked, now what I did to try to have the code for this
activity written in the activate event of sheet, on doing so it gives the
message "meathod range of object 'worksheet' failed". This is the range name
I have given the column in my sheet A. Can you help me with this?

Regards,

Durgesh.

 

[Mike H]

Hi,

Have a look here for working with worksheet named ranges.
http://www.cpearson.com/excel/named.htm

Mike