Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 5th 14, 04:38 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 26
Default 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


  #2   Report Post  
Old December 5th 14, 05:19 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,853
Default 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
  #3   Report Post  
Old December 5th 14, 06:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 26
Default 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
  #4   Report Post  
Old December 5th 14, 06:28 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,853
Default 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
  #5   Report Post  
Old December 5th 14, 07:23 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 26
Default 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


  #6   Report Post  
Old December 5th 14, 07:55 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,853
Default 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
  #7   Report Post  
Old December 5th 14, 08:39 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 26
Default 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
  #8   Report Post  
Old January 13th 21, 08:48 AM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2020
Posts: 27
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate unique values among duplicates Toby Excel Worksheet Functions 6 April 15th 09 05:55 PM
Duplicates and unique values sragor Excel Worksheet Functions 1 February 3rd 09 08:22 AM
Identifying unique values among duplicates bob Excel Worksheet Functions 4 November 10th 08 09:43 PM
How do I sum unique values among duplicates horizontally Janine Ball Excel Worksheet Functions 2 August 10th 07 12:54 PM
Identifying duplicates/unique values ali Excel Programming 5 March 6th 04 10:53 PM


All times are GMT +1. The time now is 01:08 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017