How to filter Unique values from Duplicates
 

Hi,

I have a table where one of the columns contain names of Coalfields but the column contains duplicate names.

I would like to make a separate column containing only unique names of the coalfields, sourced from the mother column.

Thanks for the help

San

How to filter Unique values from Duplicates
 

Hi,

Am Thu, 4 Dec 2014 20:38:04 -0800 (PST) schrieb San:

I have a table where one of the columns contain names of Coalfields but the column contains duplicate names.

I would like to make a separate column containing only unique names of the coalfields, sourced from the mother column.

try Advanced Filter without duplicates or copy your column to another
place and run Delete Dupicates


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to filter Unique values from Duplicates
 

On Friday, December 5, 2014 10:49:45 AM UTC+5:30, Claus Busch wrote:
Hi,

Am Thu, 4 Dec 2014 20:38:04 -0800 (PST) schrieb San:

I have a table where one of the columns contain names of Coalfields but the column contains duplicate names.

I would like to make a separate column containing only unique names of the coalfields, sourced from the mother column.

try Advanced Filter without duplicates or copy your column to another
place and run Delete Dupicates


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus

Any other method for e.g. by indexing and finding out duplicates?

San

How to filter Unique values from Duplicates
 

Hi,

Am Thu, 4 Dec 2014 22:22:20 -0800 (PST) schrieb San:

Any other method for e.g. by indexing and finding out duplicates?

your data is in column A. Then e.g. in C1:
=A1
in C2:
=IFERROR(INDEX(A:A,MATCH(1,(COUNTIF(C$1:C1,A$1:A$5 00)=0)*(A$1:A$500<""),0)),"")
Insert the array formula with CTRL+Shift+Enter and copy down till the
cell remain blank


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to filter Unique values from Duplicates
 

On Friday, December 5, 2014 11:59:00 AM UTC+5:30, Claus Busch wrote:
Hi,

Am Thu, 4 Dec 2014 22:22:20 -0800 (PST) schrieb San:

Any other method for e.g. by indexing and finding out duplicates?

your data is in column A. Then e.g. in C1:
=A1
in C2:
=IFERROR(INDEX(A:A,MATCH(1,(COUNTIF(C$1:C1,A$1:A$5 00)=0)*(A$1:A$500<""),0)),"")
Insert the array formula with CTRL+Shift+Enter and copy down till the
cell remain blank


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus.. it worked great!

Could you please explain the steps of this formula

San

How to filter Unique values from Duplicates
 

Hi,

Am Thu, 4 Dec 2014 23:23:48 -0800 (PST) schrieb San:

Could you please explain the steps of this formula

with the COUNTIF statement the values existing above the formula will be
ignored. And together with the part A$1:A$500<"" and the Match
statement the next value is found in A:A


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to filter Unique values from Duplicates
 

On Friday, December 5, 2014 1:25:09 PM UTC+5:30, Claus Busch wrote:
Hi,

Am Thu, 4 Dec 2014 23:23:48 -0800 (PST) schrieb San:

Could you please explain the steps of this formula

with the COUNTIF statement the values existing above the formula will be
ignored. And together with the part A$1:A$500<"" and the Match
statement the next value is found in A:A


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks for the explanation.

San

How to filter unique values from duplicates in excel
 

In Excel, there are several ways to filter for unique values—or remove duplicate values:

To filter for unique values, click Data Sort & Filter Advanced.
Follow these steps:

Select the range of cells, or ensure that the active cell is in a table.

Click Data Advanced (in the Sort & Filter group).
In the Advanced Filter popup box, do one of the following:

To filter the range of cells or table in place:

Click Filter the list, in-place.

To copy the results of the filter to another location:

Click Copy to another location.

In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog Button image to temporarily hide the popup window, select a cell on the worksheet, and then click Expand Button image .

Check the Unique records only, then click OK.

To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.

I hope this is helpful!
Tim Paine