Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a column in excel?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a column in excel?
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 "Durgesh M" <Durgesh wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a column in excel?
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 "Durgesh M" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a column in excel?
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" <Durgesh wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a column in excel?
Thanks Mike, this has solved my problem. Thanks again.
Regards, Durgesh. "Mike H" wrote: 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 "Durgesh M" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a column in excel?
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" wrote: 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 "Durgesh M" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a column in excel?
Hi,
Have a look here for working with worksheet named ranges. http://www.cpearson.com/excel/named.htm Mike "Durgesh M" wrote: 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" wrote: 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 "Durgesh M" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sum unique values in a column | Excel Worksheet Functions | |||
To find Multiple values in column B for a unique value in column A | Excel Worksheet Functions | |||
list unique values in a column | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
Populate a column by extracting unique values from another column? | Excel Worksheet Functions |