How to filter Unique values from Duplicates

  • Thread starter Thread starter San
  • Start date Start date
S

San

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
 
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.
Click to expand...

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


Regards
Claus B.
 
Hi,

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


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


Regards
Claus B.
Click to expand...

Thanks Claus

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

San
 
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?
Click to expand...

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$500)=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.
 
Hi,

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


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$500)=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.
Click to expand...

Thanks Claus.. it worked great!

Could you please explain the steps of this formula

San
 
Hi,

Am Thu, 4 Dec 2014 23:23:48 -0800 (PST) schrieb San:
Could you please explain the steps of this formula
Click to expand...

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.
 
Hi,

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


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.
Click to expand...

Thanks for the explanation.

San
 
Back
Top